Excel VBA to copy multiple web pages

[telmacro]

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:


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:



</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">
&nbsp;<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>
&nbsp;<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

[telmacro]

I was able to use VBA to get the numerical value of the pages into Excel. I think that might help.

So for example, if there were 5 pages
Cell A1 = 5
Cell A2 = 4
Cell A3 = 3
Cell A4 = 2

Now is it possible to click the button that matches the value of cell A1?

If so I can create a loop that does that, copies the info, deletes row 1:1
And then A:1 = 4

I can loop until Cell A:1 = ""

So how do I click on a button that is = A1?

See original post for the IE source code for the buttons.

Thanks!!

[telmacro]

I can modify the excel cell to = the hRef that I want

Ex

Instead of A1 = 5

A1 = "javascript:submitHTMLTableNavigator(400)"

Now is there some way to tell IE to go the that hRef and click on it?

I'm close, I can feel it...... (=

[telmacro]

Holy smokes, I didn't think I'd figure it out.

simple really

I've established that I was able to get an excel cell to = the href

so from there its:

Dim jExcel as String
jExcel = oExcel.Sheets("Sheet3").Range("A1")
'Where A1 = submitHTMLTableNavigator(400)
'or whatever the href is


Ie.Navigate. "javascript" & jExcel

BTW I know that the VBA in my last post doesn't quite run the way I said it does, not all of those commands are valid, but they were just what I typed in manually to see if this was all logically possible. I've updated and tested my entire code and it runs perfectly. If anyone wants info on anything I did let me know (=