I am currently working on a project that involves tracking the order statuses of individual shipments from a list of URLs stored in an Excel sheet. The code I have written is capable of looping through the URLs and extracting information from them successfully. However, I encountered a type mismatch error when attempting to add exceptions and variables to the loop for different URLs. To solve this issue, I need three specific variables to extract HTML information from the HTMLCollection/Table. Here's what I'm trying to accomplish:
- Iterate through each URL without opening new tabs in Internet Explorer.
- Retrieve the status of each item from the corresponding HTML element.
- FedEx: Delivered (td class="status")
- UPS: Delivered (id="tt_spStatus")
- USPS: Arrived at USPS Facility (class= "info-text first)
This is my current code:
Sub TrackingDeliveryStatusUpdate()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = True
Dim rngURL As Range
Dim wb1 As Workbook, ws1 As Worksheet, ws2 As Worksheet
Dim MyURL As String
Dim Rows As Long, links As Variant, IE As InternetExplorer, link As Variant
Dim i As Long
Dim sID As String
Dim rngLinks As Range, rngLink As Range
Dim filterRange As Range
Dim copyRange As Range
Set wb1 = Application.Workbooks.Open("\\S51\Store51\Employee Folders\Jason\TrackingDeliveryStatus.xls")
Set ws1 = wb1.Worksheets("TrackingDeliveryStatusResults")
Rows = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
Set rngLinks = ws1.Range("C2:C" & Rows)
With IE
.Visible = True
i = 2
For Each rngLink In rngLinks
.Navigate (rngLink)
While .Busy Or .ReadyState <> 4: DoEvents: Wend
Dim doc As Object
Set doc = IE.Document
If InStr(1, rngLink.Value2, "ups") Then
sID = "tt_spStatus"
ws1.Range("D" & i).Value = doc.getElementById(sID).Items(0).Value
End If
Next rngLink
i = i + 1
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
I initially thought this approach would work, but it seems that referencing URLs from an Excel spreadsheet causes issues. Here's a screenshot of the error message I encountered: https://i.stack.imgur.com/qC2cB.png