VBA Help (Forrmating URL)

borski88

Board Regular
Joined
Jul 3, 2015
Messages
71
HTML:
' The following code should be put in a new code module
' Requires "Microsoft Internet Controls" and "Microsoft HTML Object Library" References
Sub DownloadPPAProcessData()
    Dim Browser As InternetExplorer
    Dim Document As HTMLDocument
    Dim Table As IHTMLElement
    Dim Tables As IHTMLElementCollection
    Dim Div As IHTMLElement
    Dim Divs As IHTMLElementCollection
    Dim H3 As IHTMLElement
    Dim TR As IHTMLElement
    Dim TRs  As IHTMLElementCollection
    Dim TD As IHTMLElement
    Dim TDs  As IHTMLElementCollection
    Dim Row As Integer
    Dim Column As Integer
    
    Dim Worksheet As Worksheet
    
    Dim NodeType As String
    Dim Warehouse As String
    Dim ProcessId As String
    Dim PrimaryAttribute As String
    Dim SecondaryAttribute As String
        
    Set Worksheet = ThisWorkbook.Worksheets("Settings")
                
    startDateIntraday = Format(Worksheets("Settings").Range("E5"), "yyyy/mm/dd")
    startHourIntraday = Worksheets("Settings").Range("F5").Value
    endDateIntraday = Format(Worksheets("Settings").Range("H5"), "yyyy/mm/dd")
    endHourIntraday = Worksheets("Settings").Range("I5").Value
  
    Row = 1
    Column = 5
    
    Set Worksheet = ThisWorkbook.Worksheets("SBC_Data")
    
   ' Set Worksheet = ThisWorkbook.Worksheets.Add(After:=Worksheet)
   ' Worksheet.Name = PrimaryAttribute + ", " + SecondaryAttribute
            
       Set Browser = New InternetExplorerMedium
     Browser.Navigate "https://website.com/ppa/inspect/process?warehouseId=1&nodeType=1&processId=100106&primaryAttribute=bin_type&secondaryAttribute=container_type&startDateIntraday=" + startDateIntraday + "&startHourIntraday=" + startHourIntraday + "&endDateIntraday=" + endDateIntraday + "&endHourIntraday=" + endHourIntraday
       
    'Wait for page to load
    Do While Browser.Busy Or Browser.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    'Scan the document
    Set Document = Browser.Document
    Set Divs = Document.getElementById("secondaryProductivityList").getElementsByTagName("div")
    For Each Div In Divs
        Set H3 = Div.getElementsByTagName("h3")(0)
        
        If Not Div.className = "floatHeader" And Not H3 Is Nothing Then
            Worksheet.Cells(Row, 1).Value = H3.innerText
            Row = Row + 1
            
            Set Tables = Div.getElementsByTagName("table")
            Set Table = Tables(0)
            Set TRs = Table.getElementsByTagName("tr")
            For Each TR In TRs
                Column = 1
                Set TDs = TR.getElementsByTagName("th")
                For Each TD In TDs
                    Worksheet.Cells(Row, Column).Value = TD.innerText
                    Worksheet.Cells(Row, Column).Font.Bold = True
                    If TD.getAttribute("colspan") Then
                        Column = Column + TD.getAttribute("colspan")
                    Else
                        Column = Column + 1
                    End If
                    Next
                    
                Set TDs = TR.getElementsByTagName("td")
                For Each TD In TDs
                    Worksheet.Cells(Row, Column).Value = TD.innerText
                    Column = Column + 1
                    Next
                
            Row = Row + 1
            Next
        End If
    Row = Row + 1
    Next
    
    Browser.Quit
End Sub


I am getting an error at the "Browser.Navigate" line and I cannot understand why. Am I missing something with the URL Variables?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

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
Top