Web scrapping

lmmorris

New Member
Joined
May 1, 2017
Messages
7
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.

Code:
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?
 

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.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Try replacing that line with the following...

Code:
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.
 
Last edited:
Upvote 0
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"...

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

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