Scraping a website

[Edgar]

Glad it works for you Euler. The slowness could be because of Internet Explorer, newer browsers are optimized for that sort of website, so they load that content much faster. If you need more speed, selenium and puppeteer could be better alternatives. I've never made selenium work, though. Only puppeteer.

June7, I'm sorry it crashed for you. Maybe the while loop is too aggressive, so you can try to limit it somehow, maybe attempt to get the object only once every 500ms before reaching a timeout? Or maybe only use the shell alternative after a quick Sleep instead of spamming the ie object?

I think this would work for the Sleep to be compatible in both 32 and 64 bits:

#If VBA7 Or Win64 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As LongPtr)
#Else
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

[June7]

Removed DoEvents

If VBA7 Or Win64 Then line errors so tried each Declare. Sleep compiles but same result. Access crashes. Worse with PtrSafe.

Oh well, not like I need it.

[moke123]

Have you tried any of the FedEx API's?
https://developer.fedex.com/api/en-u...log/track.html

[isladogs]

This is the correct Sleep API code:

#If VBA7 Then 'A2010 or later 32 & 64-bit
     Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else 'A2007 or earlier
     Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long) 
#End If

In this case, only PtrSafe needs to be added
Using Win64 is superfluous as VBA7 works for both bitnesses in A2010 or later

[June7]

Tried again. This time left the # characters. Compiles but still crashes.

[Edgar]

Thanks, Colin.

[Euler]

Glad it works for you Euler. The slowness could be because of Internet Explorer, newer browsers are optimized for that sort of website, so they load that content much faster. If you need more speed, selenium and puppeteer could be better alternatives. I've never made selenium work, though. Only puppeteer.

June7, I'm sorry it crashed for you. Maybe the while loop is too aggressive, so you can try to limit it somehow, maybe attempt to get the object only once every 500ms before reaching a timeout? Or maybe only use the shell alternative after a quick Sleep instead of spamming the ie object?

I think this would work for the Sleep to be compatible in both 32 and 64 bits:

#If VBA7 Or Win64 Then
Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As LongPtr)
#Else
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

I don't think I can create an object with any other browser as the following line does with Internet Explorer:

Set ie = CreateObject("InternetExplorer.Application")

If you, or someone, knows of a way to create an object for a different browser, I'd sure like to hear about it.

Do you have to pay for Selenium/Puppeteer?

[Edgar]

You have options.
1. Stick to the built-in Internet Explorer automation.

2. Create a Fedex api key that lets you use their tracking service. Moke posted a link I had already checked, I don't know if you have to pay for this service, but since it requires me to fill a form with phone numbers, address, and all that stuff, I guess it won't be free. APIs that ask for this much information charge money.

3. Use Selenium. Selenium is supposed to be able to automate Chrome, Firefox and other browsers from VBA. It's free. Go check out some tutorials and see if you can make it work. I've tried it and it always throws errors. I suspect the problem has something to do with the browser version. I have not yet set it up because I use Puppeteer and it's just awesome.

4. Using Puppeteer is your 4th option. It requires JavaScript. Here's how I use it. Install NodeJS, which is the runtime that lets you use that library and many more. Install VSCode, which is the text editor where you'll write the code. Install the Puppeteer library. Assuming you already have a working solution that you've been testing, now deploy an executable that is agnostic of NodeJS, for that, I use another library called nexe. One command later and I have an executable. From VBA, call this executable. Make sure your Puppeteer program creates an output file that can be read by VBA. That way the whole architecture looks like this:
VBA calls the executable > The executable automates Chromium, Generates an output and closes itself > VBA reads the output.

I've done this so many times it's very normal for me. Yes, I know, it won't be easy. But you don't have to suffer, ChatGPT can help you tons. You could have a working solution in minutes from now, unlike previous years. Give the tracking API a try, maybe it's free. Or maybe someone offers a third party API that is free. Like Bing, which you can also use, but requires you to setup a VPN because nowadays it does not return the year in the delivery date for US IPs. So here's your 5th option.

5. Configure Internet Explorer to use a VPN, nowadays there are many free VPNs, so you probably can use that to make Bing search believe you're from another country that does make it return good good dates.


Here's a much shorter overview. If you end up using Puppeteer, you'll definitely do this faster, maybe it will take 2 or 3 seconds. If you use Selenium, it's probably the same time. If you stick to IE, yes, it will be a little slower, but it's working, right? you can sip some coffee and boom, done. So, basically, what I'm trying to say is that you'll gain a few seconds by using a modern browser, but are those seconds worth the efforts of learning, say, Puppeteer? or setting up a VPN? or making Selenium work? or potentially paying Fedex? Put these on a scale.

[Euler]

You have options.
1. Stick to the built-in Internet Explorer automation.

2. Create a Fedex api key that lets you use their tracking service. Moke posted a link I had already checked, I don't know if you have to pay for this service, but since it requires me to fill a form with phone numbers, address, and all that stuff, I guess it won't be free. APIs that ask for this much information charge money.

3. Use Selenium. Selenium is supposed to be able to automate Chrome, Firefox and other browsers from VBA. It's free. Go check out some tutorials and see if you can make it work. I've tried it and it always throws errors. I suspect the problem has something to do with the browser version. I have not yet set it up because I use Puppeteer and it's just awesome.

4. Using Puppeteer is your 4th option. It requires JavaScript. Here's how I use it. Install NodeJS, which is the runtime that lets you use that library and many more. Install VSCode, which is the text editor where you'll write the code. Install the Puppeteer library. Assuming you already have a working solution that you've been testing, now deploy an executable that is agnostic of NodeJS, for that, I use another library called nexe. One command later and I have an executable. From VBA, call this executable. Make sure your Puppeteer program creates an output file that can be read by VBA. That way the whole architecture looks like this:
VBA calls the executable > The executable automates Chromium, Generates an output and closes itself > VBA reads the output.

I've done this so many times it's very normal for me. Yes, I know, it won't be easy. But you don't have to suffer, ChatGPT can help you tons. You could have a working solution in minutes from now, unlike previous years. Give the tracking API a try, maybe it's free. Or maybe someone offers a third party API that is free. Like Bing, which you can also use, but requires you to setup a VPN because nowadays it does not return the year in the delivery date for US IPs. So here's your 5th option.

5. Configure Internet Explorer to use a VPN, nowadays there are many free VPNs, so you probably can use that to make Bing search believe you're from another country that does make it return good good dates.


Here's a much shorter overview. If you end up using Puppeteer, you'll definitely do this faster, maybe it will take 2 or 3 seconds. If you use Selenium, it's probably the same time. If you stick to IE, yes, it will be a little slower, but it's working, right? you can sip some coffee and boom, done. So, basically, what I'm trying to say is that you'll gain a few seconds by using a modern browser, but are those seconds worth the efforts of learning, say, Puppeteer? or setting up a VPN? or making Selenium work? or potentially paying Fedex? Put these on a scale.

I think you're right about doing the cost/benefit analysis in order to gain a couple of seconds of speed. I would like to pursue the FedEx API option, however, since my company does a lot of business with them.

You said "Moke posted a link". Could you please provide me with that link? I will check it out to see how much cost and/or hassle it would require to pursue it further. Thanks ever so much.

[June7]

Link in post 33.

[Euler]

Link in post 33.

Sorry, I didn't see that. Thanks for pointing it out.

[Euler]

You have options.
1. Stick to the built-in Internet Explorer automation.

2. Create a Fedex api key that lets you use their tracking service. Moke posted a link I had already checked, I don't know if you have to pay for this service, but since it requires me to fill a form with phone numbers, address, and all that stuff, I guess it won't be free. APIs that ask for this much information charge money.

3. Use Selenium. Selenium is supposed to be able to automate Chrome, Firefox and other browsers from VBA. It's free. Go check out some tutorials and see if you can make it work. I've tried it and it always throws errors. I suspect the problem has something to do with the browser version. I have not yet set it up because I use Puppeteer and it's just awesome.

4. Using Puppeteer is your 4th option. It requires JavaScript. Here's how I use it. Install NodeJS, which is the runtime that lets you use that library and many more. Install VSCode, which is the text editor where you'll write the code. Install the Puppeteer library. Assuming you already have a working solution that you've been testing, now deploy an executable that is agnostic of NodeJS, for that, I use another library called nexe. One command later and I have an executable. From VBA, call this executable. Make sure your Puppeteer program creates an output file that can be read by VBA. That way the whole architecture looks like this:
VBA calls the executable > The executable automates Chromium, Generates an output and closes itself > VBA reads the output.

I've done this so many times it's very normal for me. Yes, I know, it won't be easy. But you don't have to suffer, ChatGPT can help you tons. You could have a working solution in minutes from now, unlike previous years. Give the tracking API a try, maybe it's free. Or maybe someone offers a third party API that is free. Like Bing, which you can also use, but requires you to setup a VPN because nowadays it does not return the year in the delivery date for US IPs. So here's your 5th option.

5. Configure Internet Explorer to use a VPN, nowadays there are many free VPNs, so you probably can use that to make Bing search believe you're from another country that does make it return good good dates.


Here's a much shorter overview. If you end up using Puppeteer, you'll definitely do this faster, maybe it will take 2 or 3 seconds. If you use Selenium, it's probably the same time. If you stick to IE, yes, it will be a little slower, but it's working, right? you can sip some coffee and boom, done. So, basically, what I'm trying to say is that you'll gain a few seconds by using a modern browser, but are those seconds worth the efforts of learning, say, Puppeteer? or setting up a VPN? or making Selenium work? or potentially paying Fedex? Put these on a scale.

Your code to find the delivery date does a great job returning it. But how can I modify the code if it doesn't find the delivery date? I'd like to show a message saying something like:

MsgBox "No delivery date found."

I tried the following code:

Public Sub GetDeliveryDate()
    'This returns the FedEx delivery date.
    Const PREFIX_FOR_FEDEX_LINK As String = https://www.fedex.com/apps/fedextrac...rackingnumber=
    Const SUFFIX_FOR_FEDEX_LINK As String = "&cntry_code=us&locale=en_US"
    Dim ie As Object
    Dim deliveryDate As Object
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate PREFIX_FOR_FEDEX_LINK & Trim(Me!txtSampleID) & SUFFIX_FOR_FEDEX_LINK
    ie.Visible = False
    Do Until Not ie.Busy And ie.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop
    Sleep 3000
    Set deliveryDate = Nothing
    'Do While deliveryDate Is Nothing
        On Error Resume Next
        Set deliveryDate = ie.Document.getElementsByClassName("deliveryDateTextBetween")(0)
        On Error GoTo 0
    'Loop
    If IsNull(deliveryDate.INNERTEXT) Then
        Me!txtDateSampleReceived = Left(deliveryDate.INNERTEXT, InStr(deliveryDate.INNERTEXT, " ") - 1)
    Else
        MsgBox "No delivery date found.", , "Rebiotix"
    End If
    ie.Quit
    Set ie = Nothing
End Sub

I get error 91: Object variable not set.

[Euler]

I am able to get the delivery date now and I'm pursuing other methods to retrieve it faster.

Now, however, the company wants to get the Ship Date, which is the date that FedEx picked up the item. I can't find anything similar to "deliveryDateTextBetween" as found in the following line of code:

Set deliveryDate = ie.Document.getElementsByClassName("deliveryDateTextBetween")(0)

Does anyone know how I can capture that data element? Thank you very much

[Welshgasman]

Seems to me you need to inspect the page and look for the elements?

This is from the 'We Have Your Package' element.

<span class="shipment-status-progress-step-label"><div class="shipment-status-progress-step-dot">&nbsp;</div> We have your package </span>
<span class="shipment-status-progress-step-label-content">IRVING, TX</span>
<span class="shipment-status-progress-step-label-content">5/10/22 6:56 PM</span>

[Euler]

Seems to me you need to inspect the page and look for the elements?

This is from the 'We Have Your Package' element.

<span class="shipment-status-progress-step-label"><div class="shipment-status-progress-step-dot">&nbsp;</div> We have your package </span>
<span class="shipment-status-progress-step-label-content">IRVING, TX</span>
<span class="shipment-status-progress-step-label-content">5/10/22 6:56 PM</span>

I got the following code to return the shipping and delivery dates from the FedEx website but it's pretty slow:

Public Sub GetDeliveryDate()
    'https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=816437633660&cntry_code=us&locale=en_US
    On Error GoTo OOPS
    Const PREFIX_FOR_FEDEX_LINK As String = https://www.fedex.com/apps/fedextrac...rackingnumber=
    Const SUFFIX_FOR_FEDEX_LINK As String = "&cntry_code=us&locale=en_US"
    Dim ie As Object
    Dim shippingDate As Object
    Dim deliveryDate As Object
    DoCmd.Hourglass True
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate PREFIX_FOR_FEDEX_LINK & "816437633660" & SUFFIX_FOR_FEDEX_LINK
    ie.Visible = False
    Do Until Not ie.Busy And ie.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop
    Sleep 3000
    Set shippingDate = Nothing
    Set deliveryDate = Nothing
    'Do While deliveryDate Is Nothing
        'On Error Resume Next
        'Below, shipping date is the second element with that class name.
        Set shippingDate = ie.Document.getElementsByClassName("fdx-u-text--normal fdx-u-font-size--small fdx-u-line-height--large fdx-u-pl--4 fdx-u-pt--1 fdx-u-pb--1")(1)
        Set deliveryDate = ie.Document.getElementsByClassName("deliveryDateTextBetween")(0)
        'On Error GoTo 0
    'Loop
    Me!txtDateSampleShipped = Format(shippingDate.innerText, "mm/dd/yy")
    Me!txtDateSampleReceived = Format(Left(Nz(deliveryDate.innerText), InStr(deliveryDate.innerText, " ") - 1), "mm/dd/yy")
ExitSub:
    ie.Quit
    Set ie = Nothing
    DoCmd.Hourglass False
    Exit Sub
OOPS:
    Select Case Err.Number
        Case 91
            MsgBox "No delivery date found.", , "Rebiotix"
            Resume ExitSub
    End Select
End Sub

I also added error handling in case it didn't find a delivery date. I'll have to do the same for shipping date.

The following code is much faster but its drawback is that there is no year in the returned date, for some reason.

Sub GetDeliveryDate()
    On Error GoTo OOPS
    Dim req As Object
    Dim res As HTMLDocument
    Set req = CreateObject("msxml2.xmlhttp")
    req.Open "get", https://www.bing.com/packagetracking...ex&FORM=PCKTR1, False
    req.send
    Set res = CreateObject("HTMLFile")
    res.body.innerHTML = req.responseText
    'The following returns the class element that is associated with the text at the top of the web page.
    Me!txtDateSampleReceived = res.getElementsByClassName("b_focusTextSmall")(0).innerText
    'The following returns the shipping date, with no year.  There are five instances of "  pt_Cell" and we want the fifth one.
    Me!txtDateSampleShipped = res.getElementsByClassName("  pt_Cell")(4).innerText
ExitSub:
    Exit Sub
OOPS:
    MsgBox Err.Number & " " & Err.Description
    Resume ExitSub
End Sub

What I'd like to do is modify the second code block with the code from the first block so that it's both fast and returns the year in the date. So far, I've been unsuccessful. I've got this code:

Sub GetDeliveryDate()
    'This is a replica of the Bing version.
    On Error GoTo OOPS
    Const PREFIX_FOR_FEDEX_LINK As String = https://www.fedex.com/apps/fedextrac...rackingnumber=
    Const SUFFIX_FOR_FEDEX_LINK As String = "&cntry_code=us&locale=en_US"
    Dim req As Object
    Dim res As HTMLDocument
    Set req = CreateObject("msxml2.xmlhttp")
    req.Open "GET", PREFIX_FOR_FEDEX_LINK & "816437633660" & SUFFIX_FOR_FEDEX_LINK, False
    req.send
    Set res = CreateObject("HTMLFile")
    res.body.innerHTML = req.responseText
    'The following returns the class element that is associated with the text at the top of the web page.
    Me!txtDateSampleReceived = res.getElementsByClassName("deliveryDateTextBetween")(0).innerText
    'The following returns the shipping date, with no year.  There are three instances of the same class and we want the second one.
    Me!txtDateSampleShipped = res.getElementsByClassName("fdx-u-text--normal fdx-u-font-size--small fdx-u-line-height--large fdx-u-pl--4 fdx-u-pt--1 fdx-u-pb--1")(1).innerText
ExitSub:
    Exit Sub
OOPS:
    MsgBox Err.Number & " " & Err.Description
    Resume ExitSub
End Sub

But it throws an error 91: Object variable not set.

I don't know why I get that error.