Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: webquery macro navigating through website with drop down menus

  1. #1
    Seeker sonny.thind's Avatar
    Join Date
    Aug 2011
    Location
    London, Ontario
    Posts
    7
    Articles
    0

    webquery macro navigating through website with drop down menus



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Presentation1.jpg 
Views:	341 
Size:	19.4 KB 
ID:	216  

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker sonny.thind's Avatar
    Join Date
    Aug 2011
    Location
    London, Ontario
    Posts
    7
    Articles
    0
    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Presentation1.jpg 
Views:	141 
Size:	19.4 KB 
ID:	217  

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker sonny.thind's Avatar
    Join Date
    Aug 2011
    Location
    London, Ontario
    Posts
    7
    Articles
    0
    Quote Originally Posted by Ken Puls View Post
    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..

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Oh, and you need to set references to the following libraries:
    -Microsoft Internet Controls
    -Microsoft HTML Object Library
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Seeker sonny.thind's Avatar
    Join Date
    Aug 2011
    Location
    London, Ontario
    Posts
    7
    Articles
    0
    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 ..

  9. #9
    Seeker sonny.thind's Avatar
    Join Date
    Aug 2011
    Location
    London, Ontario
    Posts
    7
    Articles
    0
    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

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •