VBA - Internet Explorer Automation. (Locating data from tables)

Omen666blue

New Member
Joined
Aug 13, 2012
Messages
39
Hi guys and girls,

I have been playing with internet explorer Automation code. I have nicked some nice code that will log me in and search for what I need it too.

My next issue that i am struggling with is getting my code to Locate to a button within a table.

The table consists of several lines with different TAXI bookings for my company, I want my macro to click the amend Booking button in the specified Dates booking. I have been investigating the Source but it is different to the stuff i have been working with to log in.

I shall include my whole code and a sample of the source code for the booking.

Code:
Private Sub IE_Autiomation()
    Dim i As Long
    Dim ie As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim row As Long, col As Long
    Dim tbl As Object, obj_tbl As Object
    Dim Storage(100, 2) As String
    Dim QuestionList As IHTMLElement
Dim Questions As IHTMLElementCollection
Dim Question As IHTMLElement
 Dim RowNumber As Long
Dim QuestionId As String
Dim QuestionFields As IHTMLElementCollection
Dim QuestionField As IHTMLElement
Dim votes As String
Dim views As String
Dim QuestionFieldLinks As IHTMLElementCollection
    ' Create InternetExplorer Object
    Set ie = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    ie.Visible = False
 
    ' Send the form data To URL As POST binary request
    ie.navigate "https://cabsi.cabfind.com/Account/LoginCabsi.aspx"
 
    ' Statusbar
    Application.StatusBar = "cabsi.Cabbfind.com is loading. Please wait..."
 
    ' Wait while IE loading...
    Do While ie.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
 
    ' Find 2 input tags:
    '   1. Text field
    '   <input type="text" class="textfield" name="s" size="24" value="">
    '
    '   2. Button
    '   <input type="submit" class="button" value="">
    
    Application.StatusBar = "Search form submission. Please wait..."
 
    Set objCollection = ie.document.getelementsbytagname("input")
 
    i = 0
    While i < objCollection.Length
        If objCollection(i).Name = "ClientName" Then
 
            ' Set text for search
            objCollection(i).Value = "REMOVED LOGIN INFO"
        
        Else
        If objCollection(i).Name = "UserName" Then
 
            ' Set text for search
            objCollection(i).Value = "REMOVED LOGIN INFO"
        
        Else
         If objCollection(i).Name = "Password" Then
 
            ' Set text for search
            objCollection(i).Value = "REMOVED LOGIN INFO"
        
        Else
        
        If objCollection(i).Type = "submit" And _
               objCollection(i).Name = "btnSubmit" Then
 
                ' "Search" button is found
                Set objElement = objCollection(i)
            End If
            End If
          End If
        End If
        


      i = i + 1
 
    Wend
    objElement.Click    ' click button to search
    
    ' Wait while IE re-loading...
    Do While ie.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
 
    ' Show IE
    ie.Visible = True
    
        ' Send the form data To URL As POST binary request
    ie.navigate "https://cabsi.cabfind.com/BookingQuery.aspx"
 
    ' Statusbar
    Application.StatusBar = "cabsi.Cabbfind.com is loading. Please wait..."
 
    ' Wait while IE loading...
    Do While ie.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
    Application.StatusBar = "Search form submission. Please wait..."
 
    Set objCollection = ie.document.getelementsbytagname("input")
    i = 0
    
    While i < objCollection.Length
        If objCollection(i).Name = "ctl00$MainContent$txtClientRef" Then
 
            ' Set text for search
            objCollection(i).Value = "970"
        
        Else
        If objCollection(i).Name = "ctl00$MainContent$txtSearchByDateStartDate" Then
 
            ' Set text for search
            objCollection(i).Value = Format(Now(), "dd/mm/yyyy")
            Else
    If objCollection(i).Type = "submit" And _
               objCollection(i).Name = "ctl00$MainContent$Button1" Then
 
                ' "Search" button is found
                Set objElement = objCollection(i)
        End If
        End If
        End If
     i = i + 1
    Wend
    objElement.Click
    
    Do While ie.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
    
    'Set obj_tbl = IE.document.getelementsbytagname("table")
    row = 1
    col = 1
    a = 0
    
    Set html = ie.document
    MsgBox html.DocumentElement.innerHTML


'----------------------FROM HERE I AM HAVING ISSUES!--------------------------------  




    Set QuestionList = html.getelementsbyID("tblResults")
    Set Questions = QuestionList.Children
    For Each Question In Questions
    'if this is the tag containing the question details, process it
        If Question.className = "bookingID" Then
        'first get and store the question id in first column
            QuestionId = Replace(Question.ID, "question-summary-", "")
            Tbooker.TextBox1.Text = CLng(QuestionId)
            Set QuestionFields = Question.all
            For Each QuestionField In QuestionFields
                If QuestionField.className = "bookingID" Then
                Storage(a, 0) = QuestionField.innerText
                a = a + 1
                End If
            Next QuestionField
        End If
        Next Question
            'For Each tbl In obj_tbl
        'If tbl.ID = "tblResults" Then
           'Set TR = tbl.getelementsbytagname("TR")
           'For Each obj_row In TR
                'For Each TD In obj_row.getelementsbytagname("TD")
                  '  Storage(a, 0) = TD.innerText
                   ' a = a + 1
                    'If TD.ID = "passengerName" Then


               ' Next
                
                'row = row + 1
                'Storage(a, 1) = TD.innerText
            'Next
        
    'Next
    Tbooker.TextBox1.Text = Storage(0, 1)
    Tbooker.TextBox2.Text = Storage(0, 0)
    Tbooker.TextBox3.Text = Storage(1, 0)
    Tbooker.TextBox4.Text = Storage(1, 1)
    Tbooker.TextBox5.Text = Storage(2, 0)
    Tbooker.TextBox6.Text = Storage(2, 1)
    Tbooker.TextBox7.Text = Storage(3, 0)
    Tbooker.TextBox8.Text = Storage(3, 1)
    Tbooker.TextBox9.Text = Storage(4, 0)
    Tbooker.TextBox10.Text = Storage(4, 1)
    Tbooker.TextBox11.Text = Storage(5, 0)
    Tbooker.TextBox12.Text = Storage(5, 1)




    'getData
    Tbooker.Show
           
           
           
           
           
           
    ' Clean up
    Set ie = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
 
    Application.StatusBar = ""
End Sub

Source for the relevant page.


Code:
<div>
<div style="float: right">
    <input type="submit" name="ctl00$MainContent$Pager$btnFirst" value="First" id="MainContent_Pager_btnFirst" disabled="disabled" class="aspNetDisabled" Page="1" style="height:19px;width:40px;" />
<input type="submit" name="ctl00$MainContent$Pager$btnPrevious" value="Previous" id="MainContent_Pager_btnPrevious" disabled="disabled" class="aspNetDisabled" Page="0" style="height:19px;width:61px;" />


<input type="submit" name="ctl00$MainContent$Pager$Btn1" value="1" id="MainContent_Pager_Btn1" Page="1" style="color:Red;height:19px;width:25px;" />














<input type="submit" name="ctl00$MainContent$Pager$btnNext" value="Next" id="MainContent_Pager_btnNext" disabled="disabled" class="aspNetDisabled" Page="2" style="height:19px;width:40px;" />
<input type="submit" name="ctl00$MainContent$Pager$btnLast" value="Last" id="MainContent_Pager_btnLast" disabled="disabled" class="aspNetDisabled" Page="1" style="height:19px;width:61px;" />  Total pages:1
</div>


<div id="divResults" style="float: left">
  
    <table id="tblResults" >
        
   
        <thead><tr><th></th><th>Booking Ref</th><th>Pickup Location</th><th>Passenger Name</th><th>Destination Location</th><th>Date Time</th><th>Status</th><th colspan="3">Options</th></tr></thead>
      
         <tr>
             <td rowspan="3">
                 <button type="button" class="btn_view" *******="ViewBookingDetails(4713706);">View</button>
             </td>
            <td rowspan="3"><div id="bookingID">4713706</div></td>            
            <td rowspan="3"><div id="pickupAddress"> ST LEONARDS WEST MARINA DEPOT, CLIFTONVILLE RD, HASTINGS, TN38 8AG</div></td>
            <td rowspan="3"><div id="passengerName"> LTP HH970</div></td>
            <td rowspan="3">HASTINGS STATION PICK UP BY COLLEGE ENTRANCE, STATION APPROACH, HASTINGS, TN34 1BA</td>
            <td rowspan="3">03/03/2017 20:04</td>            
            <td rowspan="3"><span class="statusConfirmed">Confirmed</span></td>    
           
            <td><a *******="return confirm('Are you sure you want to Amend this booking?');" id="MainContent_rptBookings_btnAmendBooking_0" class="btn_amend" href="javascript:__doPostBack('ctl00$MainContent$rptBookings$ctl01$btnAmendBooking','')">
             Amend</a></td>
         </tr>
            <tr><td><a id="MainContent_rptBookings_btnBookReturn_0" class="btn_return" href="javascript:__doPostBack('ctl00$MainContent$rptBookings$ctl01$btnBookReturn','')">Return</a></td></tr>                              
            <tr><td><a *******="return confirm('Are you sure you want to Cancel this booking?');" id="MainContent_rptBookings_btnCancelBooking_0" class="btn_cancel_ASI" href="javascript:__doPostBack('ctl00$MainContent$rptBookings$ctl01$btnCancelBooking','')">
                Cancel</a></td></tr>
         
         
      
         <tr>
             <td rowspan="3">
                 <button type="button" class="btn_view" *******="ViewBookingDetails(4713705);">View</button>
             </td>
            <td rowspan="3"><div id="bookingID">4713705</div></td>            
            <td rowspan="3"><div id="pickupAddress"> ST LEONARDS WEST MARINA DEPOT, CLIFTONVILLE RD, HASTINGS, TN38 8AG</div></td>
            <td rowspan="3"><div id="passengerName"> LTP HH970</div></td>
            <td rowspan="3">HASTINGS STATION PICK UP BY COLLEGE ENTRANCE, STATION APPROACH, HASTINGS, TN34 1BA</td>
            <td rowspan="3">02/03/2017 20:04</td>            
            <td rowspan="3"><span class="statusConfirmed">Confirmed</span></td>    
           
            <td></td>
         </tr>
            <tr><td><a id="MainContent_rptBookings_btnBookReturn_1" class="btn_return" href="javascript:__doPostBack('ctl00$MainContent$rptBookings$ctl02$btnBookReturn','')">Return</a></td></tr>                              
            <tr><td></td></tr>
         
         
      
 
</table> 
    <br />
<div style="float: right">
   
    <input type="submit" name="ctl00$MainContent$Pager2$btnFirst" value="First" id="MainContent_Pager2_btnFirst" disabled="disabled" class="aspNetDisabled" Page="1" style="height:19px;width:40px;" />
<input type="submit" name="ctl00$MainContent$Pager2$btnPrevious" value="Previous" id="MainContent_Pager2_btnPrevious" disabled="disabled" class="aspNetDisabled" Page="0" style="height:19px;width:61px;" />


<input type="submit" name="ctl00$MainContent$Pager2$Btn1" value="1" id="MainContent_Pager2_Btn1" Page="1" style="color:Red;height:19px;width:25px;" />














<input type="submit" name="ctl00$MainContent$Pager2$btnNext" value="Next" id="MainContent_Pager2_btnNext" disabled="disabled" class="aspNetDisabled" Page="2" style="height:19px;width:40px;" />
<input type="submit" name="ctl00$MainContent$Pager2$btnLast" value="Last" id="MainContent_Pager2_btnLast" disabled="disabled" class="aspNetDisabled" Page="1" style="height:19px;width:61px;" />  Total pages:1
</div>
    
</div>

I need to firstly get the Code to Locate to the Booking information Name, Pickup local + destination and print this to the user form Text boxes. and then locate that bookings ##class="btn_amend"## button and click it. From there i am back to the normal forms for the website we use here at work.

Thanks for looking at this, If you need any more information or source code let me know.
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top