webquery macro navigating through website with drop down menus

**sonny.thind** · 2011-08-24, 07:15 PM

I am trying to write a web query through a secure website (cannot provide the log in data) "https://services.cuberoute.com/gcdp/html/home"
which enables me to get info from different agencies on different dates. But I am not able to find any Macro which can
1) get past the log in screen.
2) select a drop down menu for the agency I need
3) select a date from the calendar available and then download the table into excel
see attached
can someone provide me with a reference where I can find this ? I am using chrome and Internet Explorer.

**Ken Puls** · 2011-08-24, 07:32 PM

The last time I tried this, I recall it being very tricky, and I can't remember if I actually got it to work. As I recall, I needed to bind to a DOM object model in IE to do it.

I'm not sure if you find this of any help, but maybe it will get you on the right path: http://vba-corner.livejournal.com/4623.html

(Honestly, I get the security part, but will be tough to help and test without credentials to see if it works. We may have to ride shotgun and just offer advice.)

**sonny.thind** · 2011-08-24, 09:27 PM

Ok I found this on a site and tried it out ,However I am getting an error (see attached)

Code:

 
Sub LoginToSite()
Dim IE As IE_Class
Dim IEdoc As Object
Dim Password As Object
Dim SubmitBtn As Object
Dim UserName As Object
 
Set IE = New IE_Class
IE.app.Visible = True
 
URL = "https://services.cuberoute.com/gcdp/html/home"
IE.app.Navigate URL
 
While IE.app.Busy Or IE.app.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
'IE.WaitForDownload
'IE.WaitTillReady
 
Set IEdoc = IE.app.Document
Set UserName = IEdoc.getElementsByName("user")(0)
Set Password = IEdoc.getElementsByName("password")(0)
 
UserName.Value = "Me"
Password.Value = "123"
 
Set SubmitBtn = IEdoc.getElementsByTagName("input")(2)
SubmitBtn.Click
 
End Sub

I have also added the web query I recorded but when I try to connect I cannot get past the log in and have to add in all the info again.

Code:

 
Sub WebQuery()
' Macro1 Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://services.cuberoute.com/gcdp/oe/view_orders_deliveries", _
        Destination:=Range("$A$1"))
        .Name = "view_orders_deliveries"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 60
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """deliveryOrdersSearchTable"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Range("A1:M169").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Range("P5").Select
End Sub

**Ken Puls** · 2011-08-25, 07:21 AM

Yeah, I'm thinking that the standard web query won't do it, as you can't feed it the username/password.

I did a search just now, and found this one at bigresource.com (Tough site to navigate I find, but hopefully this will help.)

Code:

Public Sub WebAccess()
    Dim strUid As String    'UserID
    Dim strPass As String    'Password
    Dim strQueryURL As String    'URL to query
    Dim objIE As New SHDocVw.InternetExplorer
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim htmlInput As MSHTML.HTMLInputElement
    Dim htmlColl As MSHTML.IHTMLElementCollection
    Dim testing As String
 
    strUid = "YourId"
    strPass = "YourPass"
    Set objIE = CreateObject("InternetExplorer.Application")
    With objIE
        .navigate "Your URL"    'Main URL
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        Set htmlDoc = .document
        Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
 
        For Each htmlInput In htmlColl
            If htmlInput.Name = "Itsname" Then htmlInput.Value = strUid
            If htmlInput.Name = "Itsname" Then htmlInput.Value = strPass
        Next htmlInput
        For Each htmlInput In htmlColl
            If htmlInput.Name = "Itsname" Then
                htmlInput.Click
                Exit For
            End If
        Next htmlInput
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
    End With
    strQueryURL = objIE.LocationURL
    Call ConnettionSUB(strQueryURL)
 
    Set htmlDoc = Nothing
    objIE.Quit
    Set objIE = Nothing
End Sub

The challenge I see here though, is that the users calls another macro, which he didn't provide, at the end to get his table. I'm not sure that's such a big issue though... So long as we can get you logged in, we should be able to query IE to find out what the contents of the page are, and pull that back to Excel. The big key first is to getting logged in successfully...

Let me know if this gets you any closer...

**sonny.thind** · 2011-08-25, 03:25 PM

Originally Posted by Ken Puls

Yeah, I'm thinking that the standard web query won't do it, as you can't feed it the username/password.

I did a search just now, and found this one at bigresource.com (Tough site to navigate I find, but hopefully this will help.)

Code:

Public Sub WebAccess()
    Dim strUid As String    'UserID
    Dim strPass As String    'Password
    Dim strQueryURL As String    'URL to query
    Dim objIE As New SHDocVw.InternetExplorer
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim htmlInput As MSHTML.HTMLInputElement
    Dim htmlColl As MSHTML.IHTMLElementCollection
    Dim testing As String
 
    strUid = "YourId"
    strPass = "YourPass"
    Set objIE = CreateObject("InternetExplorer.Application")
    With objIE
        .navigate "Your URL"    'Main URL
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        Set htmlDoc = .document
        Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
 
        For Each htmlInput In htmlColl
            If htmlInput.Name = "Itsname" Then htmlInput.Value = strUid
            If htmlInput.Name = "Itsname" Then htmlInput.Value = strPass
        Next htmlInput
        For Each htmlInput In htmlColl
            If htmlInput.Name = "Itsname" Then
                htmlInput.Click
                Exit For
            End If
        Next htmlInput
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
    End With
    strQueryURL = objIE.LocationURL
    Call ConnettionSUB(strQueryURL)
 
    Set htmlDoc = Nothing
    objIE.Quit
    Set objIE = Nothing
End Sub

The challenge I see here though, is that the users calls another macro, which he didn't provide, at the end to get his table. I'm not sure that's such a big issue though... So long as we can get you logged in, we should be able to query IE to find out what the contents of the page are, and pull that back to Excel. The big key first is to getting logged in successfully...

Let me know if this gets you any closer...

Thanks Ken,
Ok say we ignore the log in step since that is seeming to be the tough part--and we ask the the users to log in the first time. will IE regognize the logins and then allow us to continue to access the data?. The specification for the agency and date can come from a spreadsheet required (sheet 2). Is there a waythen I can access the agency I need (from a second macro)and download the tables they provide then.
See this is an everyday report for 6 to 7 different agencies and I have to perform the analysis everyday which is the tough part as I have to log in everytime select the data and then import.. which is really awaste of time if this can be automated..
hope I am making sense here..

**Ken Puls** · 2011-08-25, 06:28 PM

We can try that. I knocked up the following code to grab the IE windows, but it's not bringing in the document content. I don't have time to look any more this morning though, so hopefully you can fight with it a bit and get somewhere. If not, I'll try and loop back on it later tonight.

Code:

Sub ExplorerTest()
    Dim sURL_to_Retrieve As String
    Dim appIE As SHDocVw.InternetExplorer
    'Check if page is already open
    sURL_to_Retrieve = "http://www.excelguru.ca"
    Set appIE = GetOpenIEByURL(sURL_to_Retrieve, False)
    If appIE Is Nothing Then
        'No IE instance open.  Tell user
        MsgBox "Sorry, but internet explorer isn't open." & vbNewLine & _
               "Please open IE and log in to the site before" & vbNewLine & _
               "running this macro.", vbInformation + vbOKOnly, "IE is not open!"
    End If
    'Get the HTML from the page
    With appIE
        'Where the heck is the document content???
        .Refresh
    End With
    Set appIE = Nothing
End Sub
Function GetOpenIEByURL(sURL As String, Optional ByVal bExactMatch As Boolean = True) As SHDocVw.InternetExplorer
'Finds an open IE site by checking the URL

    Dim objShellWindows As SHDocVw.ShellWindows
    Dim lCount As Long
    
    Set objShellWindows = New SHDocVw.ShellWindows
    
    If bExactMatch = False Then sURL = "*" & sURL & "*"
    'Ignore errors when accessing the document property
    On Error Resume Next
    'Loop through all open Shell-Windows
    For lCount = 1 To objShellWindows.Count
        'Only look at HTML documents
        If TypeName(objShellWindows.Item(lCount - 1).Document) = "HTMLDocument" Then
            'Check the URL
            If objShellWindows.Item(lCount - 1).LocationURL Like sURL Then
                'Exit the function, since we found the right URL to work with
                Set GetOpenIEByURL = objShellWindows.Item(lCount - 1)
                Exit Function
            End If
        End If
    Next
End Function

**Ken Puls** · 2011-08-25, 06:31 PM

Oh, and you need to set references to the following libraries:
-Microsoft Internet Controls
-Microsoft HTML Object Library

**sonny.thind** · 2011-08-26, 03:45 AM

I cant seem to get through.. It keeps giving me the ""Sorry, but internet explorer isn't open." error at the beginning even though I have the site logged in ..

**sonny.thind** · 2011-08-27, 03:39 PM

Hi,

So I got this Macro from another site and it seems to get through the login page(great!). But How Do I link it to the web query I created?
I still do need to navigate through the site (multiple windows) to get the tables I need.?

Code:

Sub LoginToSite2()
  Dim IE As Object
  Dim IEdoc As Object
  Dim Password As Object
  Dim SubmitBtn As Object
  Dim UserName As Object
  
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
      URL = "https://services.cuberoute.com/gcdp/html/home"
      IE.Navigate URL
      
      While IE.Busy Or IE.readyState <> 4: DoEvents: Wend
      
      Set IEdoc = IE.Document
        Set UserName = IEdoc.getElementsByName("user")(0)
        Set Password = IEdoc.getElementsByName("password")(0)
        
          UserName.Value = "Me"
          Password.Value = "123"
      
          Set SubmitBtn = IEdoc.getElementsByTagName("input")(2)
          SubmitBtn.Click
          
End Sub

**Ken Puls** · 2011-08-28, 06:01 AM

Hi Sonny,

Great to hear that you're making progress with this. It's also starting to look very finicky to me. It seems that the websites all treat the elements slightly different when logging in.

To carry this forward, I'm a little curious on how familiar you are with coding and debugging in VBA. One reason I ask is that you don't force variable declaration... Your routine didn't compile for me when I copied it because there was no "Dim URL as String" line. I'd highly recommend you go into your VBA options and set the flag for variable declaration to true. It will put the words "Option Explicit" at the top of every new code module, and this forces you to actually write the Dim as lines. (For existing modules you need to type it in manually.) It can prevent certain issues like variable mispelling from happening.

The other reason I ask is around debugging stuff. The steps I would use next is, once I'm logged in, I'd re-set the ieDoc to the document again, then I'd check out the properties of that document in the locals windows. (If you've never done this, display the locals window first. You'll see all of your variables there, and you can click the little + to expand them and see what properties exist, and what their values are. This may help as far as finding out what elements of the document you can read.

Let me know if that gets you anywhere or no...