Web scrapping

[lmmorris]

Hi,

I'm trying to pull a data table from an intranet webpage based on the data entered into a cell, certain time frame, but keep getting a compile error message when I try to run the VBA.

Dim httpRequest As XMLHTTP      'XML V6.0
Dim DataObj As New MSForms.DataObject   'Forms 2.0
Dim URL$

strtDte = Sheets("Boss Wash").Range("Q35")
endDte = Sheets("Boss Wash").Range("Q37")
strtHr = Sheets("Boss Wash").Range("K35")
strtMin = Sheets("Boss Wash").Range("L35")
endHr = Sheets("Boss Wash").Range("K37")
endMin = Sheets("Boss Wash").Range("L37")

https://fclm-portal.amazon.com/reports/processPathRollup?reportFormat=HTML&warehouseId=CLT2&maxIntradayDays=1&spanType=Intraday&startDateIntraday=" & strtDte & "startHourIntraday=" & strtHr & "&startMinuteIntraday=" & strtMin & "&endDateIntraday=' & endDte & "&endHourIntraday=" & endHr & "&endMinuteIntraday=" & endMin & "&adjustPlanHours=true&_adjustPlanHours=on&hideEmptyLineItems=true&_hideEmptyLineItems=on&employmentType=AllEmployees
Set httpRequest = New XMLHTTP
httpRequest.Open "GET", URL, False
httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
httpRequest.send ""

' ... after the .send call finishes, you can check the server's response:
'Debug.Print httpRequest.Status
'Debug.Print httpRequest.statusText

DataObj.SetText httpRequest.responseText
DataObj.PutInClipboard

With Sheets("PPR")
    .Activate
    .Cells.Clear
    .Cells(1, 1).Select
    .PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    
End With

Can someone help me figure out what I'm doing wrong?

 

[Domenic]

Make sure that you have references checked for the following object libraries (VBE > Tools > References)...

1) Microsoft XML, v6.0

2) Microsoft Forms 2.0 Object Library

 

[lmmorris]

Make sure that you have references checked for the following object libraries (VBE > Tools > References)...

1) Microsoft XML, v6.0

2) Microsoft Forms 2.0 Object Library

I got the first one but would Microsoft Office 15.0 Object Library work for the second?

 

[lmmorris]

Make sure that you have references checked for the following object libraries (VBE > Tools > References)...

1) Microsoft XML, v6.0

2) Microsoft Forms 2.0 Object Library

Ok so I fixed that part but now it's giving the Compile Error in regards to the web address.

 

[Domenic]

Try replacing that line with the following...

URL = "https://fclm-portal.amazon.com/reports/processPathRollup?reportFormat=HTML&warehouseId=CLT2&maxIntradayDays=1&spanType=Intraday&startDateIntraday=" & strtDte & "startHourIntraday=" & strtHr & "&startMinuteIntraday=" & strtMin & "&endDateIntraday=" & endDte & "&endHourIntraday=" & endHr & "&endMinuteIntraday=" & endMin & "&adjustPlanHours=true&_adjustPlanHours=on&hideEmptyLineItems=true&_hideEmptyLineItems=on&employmentType=AllEmployees"

Notice that I also replaced an apostrophe (') with a quote (") within the string.

 

[lmmorris]

IT WORKS! I'm stupidly excited!!

Thanks!

 

[Domenic]

You're very welcome! Glad I could help!

Cheers!

 

[lmmorris]

You're very welcome! Glad I could help!

Cheers!

I spoke too soon. The intranet page is not pulling the correct start and end dates. It is defaulting the start date and time to midnight tonight.

 

[Domenic]

It looks like the dates and times will need to be formatted before assigning them to your variables. For example, if the dates need to be in the format "yyyy-mm-dd"...

strDte = Format(Sheets("Boss Wash").Range("Q35").Value, "yyyy-mm-dd")

Also, make sure that all variables relating to a date and time are declared as a String. And, also, make sure that the dates and times in your worksheet are true date and time values.