Excel VBA to copy multiple web pages
Hi guys,
I've got a bit of experience with VBA, but not interacting with Internet Explorer.
I have a VBA code that opens a web site, runs a search, and copies everything to Excel:
Code:
Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open("C:\Documents and Settings\abc.xls")
oExcel.Visible = True
Dim PostData() As Byte
Dim UserName As String
Dim Password As String
Dim LogonForm As HTMLFormElement
Dim SubmitInput As HTMLInputElement
UserName = "xxxx"
Password = "xxxx"
With Session
Dim ie As InternetExplorer
Set ie = New InternetExplorer
With ie
.Navigate "http:abcd"
.Visible = True
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until .document.readyState = "complete"
DoEvents
Loop
Set LogonForm = .document.LogonForm
With LogonForm
.UserName.Value = UserName
.Password.Value = Password
For Each SubmitInput In .getElementsByTagName("INPUT")
If InStr(SubmitInput.getAttribute("onclick"), "AUTHENTICATE") Then
SubmitInput.click
Exit For
End If
Next
End With
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until .document.readyState = "complete"
DoEvents
Loop
Dim Doc As HTMLDocument
'Set Doc = ie.document
Dim frm
Dim drp
Set frm = .document.forms("csrMainMenuForm")
Set drp = frm.all("ordersperpage")
drp.Item(9).Selected = True
With Doc.getElementsByName("checkBoxList(OrderStatus)")
Set drp1 = ie.document("ordersPerPage")
drp1.Item(3).Selected = True
Set csrMainMenuFormB = .document.csrMainMenuForm
With csrMainMenuFormB
For Each SubmitInput In .getElementsByTagName("INPUT")
If InStr(SubmitInput.getAttribute("onclick"), "ADVANCED_SEARCH") Then
SubmitInput.click
Exit For
End If
Next
End With
Do Until ie.readyState = READYSTATE_COMPLETE
DoEvents
Loop
Do Until .document.readyState = "complete"
DoEvents
Loop
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
oExcel.Range("A1").Select
oExcel.ActiveSheet.Paste
This may not be the best looking code, but it works.
What I need to do now, is check that there are no more pages of info returned in my search.
There are generally around 4 pages of data.
If there are more pages, it needs to
go to that page, and do the whole select all, copy and paste functions again.
But this time to a new sheet in excel.
When there are no more pages of data, it can close IE.
I don't know where to go from here?
Here is what I believe to be the source code from the web site that shows if there are multiple pages:
HTML Code:
</table>
<table border="0" width="100%">
<tr>
<td align="left">
<img border="0" src="/images/htmltable/first_dis.gif">
<img border="0" src="/images/htmltable/left_dis.gif">
<b>1</b>
<a href="javascript:submitHTMLTableNavigator(100)">2</a>
<a href="javascript:submitHTMLTableNavigator(200)">3</a>
<a href="javascript:submitHTMLTableNavigator(300)">4</a>
<a href="javascript:submitHTMLTableNavigator(400)">5</a>
<img border="0" src="/images/htmltable/right_dis.gif">
<a href="javascript:submitHTMLTableNavigator(400)"><img border="0" src="/images/htmltable/last.gif"></a>
</td>
<td align="right">
Displaying 1 - 100 of 466
</td>
</tr>
</table>
Any ideas?
Thank you everyone
