Scraping a website

[Euler]

I couldn't send a header to make this url return a different date format:
https://www.bing.com/packagetracking...ex&FORM=PCKTR1
However, using my VPN, I was able to see that the date format changes according to the IP. So using a VPN will let you return a good date.

The second website sends a series of http requests while loading the data, so our http request procedure will only bring the initial site before it loads stuff. For this kind of workflow, a browser automation is better. However, I tested that it fails quite often because of it being internet explorer (or maybe I sent too many requests too fast). You could however use Selenium or Puppeteer for that. Or use another method. Having the VPN wouldn't be a bad idea, bing can give you a good date if you use an IP outside the US. It would save you a lot of time.

In order to get the text with class "deliveryDateTextBetween", you would use:
Document.getElementsByClassName("deliveryDateTextB etween")(0).innerText in VBA
Document.getElementsByClassName("deliveryDateTextB etween")[0].innerText in JS

Don't give up with VBA, though. There are other approaches to take. Maybe you can open the IE window, then grab it using shell and get its document element to inspect it.

Thanks so much again, Edgar, for taking the time to look at this problem of mine. I will try the VBA code you suggested.

[June7]

Tried using FedEx URL and get "Object variable or With block variable not set" error on:
Debug.Print res.getElementsByClassName("deliveryDateTextBetwee n")(0).innerText


I can:
Debug.Print req.responseText
Delivery info does not show in the output.


So how does VBA capture the info as done with: right click on the website page and select "Inspect" I get a window showing the HTML code, I think. When I right click on the Elements window and select "Copy" then "Copy outerHTML"

[Edgar]

June7, you mentioned the reason yourself for the fedex url to not work with a simple http request. This one requires browser automation because their app sends additional http requests that help come up with the result. Trying to catch all those requests and faking them from VBA is not worth it, better to just use some browser automation. Also, for getElementsByClassName to work, there must be a document to inspect, you got the error because there was none. It was not set. Once you get access to the document element, you can query it easily using the posted snippet.

The following code did work on my end, but you need to not spam the server. Try to use it once every minute at least.

Option Compare Database
Option Explicit

Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

Sub getFedexDate2()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    
    ie.navigate "https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=581190049992&cntry_code=us&locale=en_US"
    ie.Visible = True
    
    Do Until Not ie.Busy And ie.ReadyState = 4
        DoEvents
    Loop

    Sleep 3000
    
    Dim deliveryDate As Object
    Set deliveryDate = Nothing
    
    Do While deliveryDate Is Nothing
        On Error Resume Next
        Set deliveryDate = ie.Document.getElementsByClassName("deliveryDateTextBetween")(0)
        On Error GoTo 0
    Loop
    
    Debug.Print deliveryDate.innerText
    
    ie.Quit
    Set ie = Nothing
End Sub

What it does is open ie and navigate to the url. Then, using shell, get the window you opened and attempt to set the html element until it appears. It also waits 3 seconds for good measure. I hope it gives you some results.

[Welshgasman]

That last code works for me, even with MS opening another tab amd a warning message that IE is out of support.

[Edgar]

That last code works for me, even with MS opening another tab amd a warning message that IE is out of support.

Excellent news! I tested it on Access 2016, you use 2007, right?

The following lines can probably be commented out and it should still work, but it's good to be cautious.

    Do Until Not ie.Busy And ie.ReadyState = 4
        DoEvents
    Loop

    Sleep 3000

[Welshgasman]

Yes Edgar, I am on 2007. It is enough for my needs.

[Edgar]

What the... I noticed I posted the version that doesn't use a shell window. Anyway, if anyone's wondering, here's that snippet too.

Sub getFedexDate()
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer
    
    ie.navigate "https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=581190049992&cntry_code=us&locale=en_US"
    ie.Visible = True
    
'    Sleep 3000
    
    Dim sh As Object
    Set sh = CreateObject("shell.application")

    Dim deliveryDate As Object
    Set deliveryDate = Nothing
    
    Do While deliveryDate Is Nothing
        On Error Resume Next
        Set deliveryDate = sh.windows.Item(0).Document.documentElement.getElementsByClassName("deliveryDateTextBetween")(0)
        On Error GoTo 0
    Loop
    
    Debug.Print deliveryDate.innerText
    
    ie.Quit
    Set ie = Nothing
End Sub

Hey, that confirms it, they both work as long as you don't spam the server.

[Welshgasman]

I needed to add MS Internet Controls to get that one to work.

[Edgar]

You're right, Gasman, that's on my reference list, I don't remember adding it though. Something added it.

[Euler]

Tried using FedEx URL and get "Object variable or With block variable not set" error on:
Debug.Print res.getElementsByClassName("deliveryDateTextBetwee n")(0).innerText


I can:
Debug.Print req.responseText
Delivery info does not show in the output.


So how does VBA capture the info as done with: right click on the website page and select "Inspect" I get a window showing the HTML code, I think. When I right click on the Elements window and select "Copy" then "Copy outerHTML"

That's my question. Is there a way for VBA to capture the data in the outerHTML. I manually copied the outerHTML to Word. It's over 900 pages but the information I want is in there.

[Euler]

This works great, Edgar. Thanks so much for your expertise.

[Euler]

For some reason "Sleep 3000" doesn't compile. I will have to research that.

[isladogs]

Did you include the Sleep API code? If you did and are using 64-bit Access, it will need updating using PtrSafe

[June7]

Glad it works for Euler, crashed Access for me.
I already have Microsoft Internet Controls reference library selected.
Sleep 3000 does not compile for me either.

I added DoEvents and the page eventually loads after error message "Fedex is not responding due to a long-running script." but the data is never pulled. VBA is frozen (infinite loop?). I have to kill with Task Manager.

[Euler]

Yes, I found the API code and entered "PtrSafe" so it would work. Now, it works just fine, though a little slow compared to the original code.
I deeply appreciate the efforts of everyone who spent their time with this. Thank you very much.