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 (=