Hi,
I’m seeking guidance on working backwards from a vba code that was built into a tool by a remote coworker that I can adjust myself.
The vba code calls on other scripts from within the same document to get “store address” and other pertinent information to run the Main script - the one I’m trying to figure out how to modify. So I’m only including the main script here and not the other ones as I feel this is the one I need to adjust.
Simply put, from what I can tell this main script goes to a website to call the html source code from that site and pull one piece of pertinent information from that website that is dynamic. As the website being called on, reports different information for each store, it completes the process over and over using the other built in vba codes to pull the store address in its search and returns that pertinent information for each store address. The reason I need to figure out how to adjust this script is because randomly the website changes where it stores the pertinent information in the html code. So rather than wait on the co worker to adjust the script, I want to be able to adjust it myself.
Here are the things I’m trying to figure out:
1) what part of this code is what is actually determining what information to pull from the html source code?
2) from the website itself, how can I pull the source code to determine and choose “this piece of information on the website is XX in the code and therefore I put that XX in the vba code to be called ? I’ve tried using inspect source in chrome or viewing html source code from the website and comparing to elements in the vba script to try and figure it out myself but I’m not having any luck.
I have had the co worker adjust the script for me before whenever the website changed. So when I compared the two scripts side by side, all I could see that my co worker changed was the number
i is equaled to. I’m posting both the original script and the one he changed in case that helps.
I know this is vague. So please let me know what other information I could provide to help.
Thanks.
New code
old code
I’m seeking guidance on working backwards from a vba code that was built into a tool by a remote coworker that I can adjust myself.
The vba code calls on other scripts from within the same document to get “store address” and other pertinent information to run the Main script - the one I’m trying to figure out how to modify. So I’m only including the main script here and not the other ones as I feel this is the one I need to adjust.
Simply put, from what I can tell this main script goes to a website to call the html source code from that site and pull one piece of pertinent information from that website that is dynamic. As the website being called on, reports different information for each store, it completes the process over and over using the other built in vba codes to pull the store address in its search and returns that pertinent information for each store address. The reason I need to figure out how to adjust this script is because randomly the website changes where it stores the pertinent information in the html code. So rather than wait on the co worker to adjust the script, I want to be able to adjust it myself.
Here are the things I’m trying to figure out:
1) what part of this code is what is actually determining what information to pull from the html source code?
2) from the website itself, how can I pull the source code to determine and choose “this piece of information on the website is XX in the code and therefore I put that XX in the vba code to be called ? I’ve tried using inspect source in chrome or viewing html source code from the website and comparing to elements in the vba script to try and figure it out myself but I’m not having any luck.
I have had the co worker adjust the script for me before whenever the website changed. So when I compared the two scripts side by side, all I could see that my co worker changed was the number
i is equaled to. I’m posting both the original script and the one he changed in case that helps.
I know this is vague. So please let me know what other information I could provide to help.
Thanks.
New code
Code:
Sub GetUpdate()
Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLele As MSHTML.IHTMLElementCollection
Dim site As String
Dim SlowDown As Long
Dim TN As Date
Dim i As Long
Dim b As Long
Dim NumofRest As Long
ActiveSheet.Range("L5").Value = ("Running Now!")
Call Uptime1
TN = Now()
Call CC
GoSlow
Application.ScreenUpdating = True
Application.Wait (Now + TimeValue("0:00:02"))
Application.ScreenUpdating = False
SelectRest
Application.Calculation = xlCalculationManual
DoEvents
Application.DisplayAlerts = False
On Error Resume Next
'i = 30
'i = 48
i = 33
b = 1
Dim Cntr As Long
Cntr = Selection.Count
NumofRst = 1
Application.EnableCancelKey = xlErrorHandler
Application.ScreenUpdating = True
Application.ScreenUpdating = False
For Each Rest In Selection
site = ""
DoEvents
Rest.Offset(0, 3) = ""
site = Application.WorksheetFunction.VLookup(Rest, Range("UEList"), 2, 0)
If site = "" Or site = "https://www.ubereats.com/stores/" Then
Rest.Offset(0, 3) = " Web Address Unknown"
GoTo Nrest
End If
DoEvents
XMLPage.Open "GET", site, False
XMLPage.Send
HTMLDoc.body.innerHTML = XMLPage.responseText
Rest.Offset(0, 2) = HTMLDoc.getElementsByTagName("h1")(0).innerText
Set HTMLele = HTMLDoc.getElementsByTagName("div")
' If HTMLele(i).innerText = "BreakfastLunchDinner" Then
' i = 30
' End If
'
' If HTMLele(i).innerText = "English" Then
' i = 30
' End If
'
' If HTMLele(i).innerText = "BreakfastLunch & Dinner" Then
' i = 30
' End If
'
' If HTMLele(i).innerText = "" Then
' i = 30
' End If
Rest.Offset(0, 3) = HTMLele(i).innerText
DoEvents
'
Application.StatusBar = "Updating " & NumofRst & _
" of " & Cntr & " | Started at " & TN & " | Restaurant # " & Rest
NumofRst = NumofRst + 1
Nrest:
Next Rest
Application.DisplayAlerts = True
On Error GoTo 0
SortU
ActiveSheet.Range("L5").Value = ""
Range("TStamp") = Now
Application.Wait (Now + TimeValue("0:00:01"))
Range("A2").Select
Application.Calculation = xlCalculationAutomatic
Call Uptime2
Application.ScreenUpdating = True
Application.StatusBar = False
Application.Wait (Now + TimeValue("0:00:02"))
Range("A2").Select
GoSlow
MsgBox "Update complete!" & vbCr & "Started at " & TN & vbCr & "Ended at " & Now(), , "Good News"
End Subold code
Code:
Sub GetUpdate()
Dim XMLPage As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument
Dim HTMLele As MSHTML.IHTMLElementCollection
Dim site As String
Dim SlowDown As Long
Dim TN As Date
Dim i As Long
Dim b As Long
Dim NumofRest As Long
ActiveSheet.Range("L5").Value = ("Running Now!")
Call Uptime1
TN = Now()
Call CC
GoSlow
Application.ScreenUpdating = True
Application.Wait (Now + TimeValue("0:00:02"))
Application.ScreenUpdating = False
SelectRest
Application.Calculation = xlCalculationManual
DoEvents
Application.DisplayAlerts = False
On Error Resume Next
' i = 30
i = 48
b = 1
Dim Cntr As Long
Cntr = Selection.Count
NumofRst = 1
Application.EnableCancelKey = xlErrorHandler
Application.ScreenUpdating = True
Application.ScreenUpdating = False
For Each Rest In Selection
site = ""
DoEvents
Rest.Offset(0, 3) = ""
site = Application.WorksheetFunction.VLookup(Rest, Range("UEList"), 2, 0)
If site = "" Or site = "https://www.ubereats.com/stores/" Then
Rest.Offset(0, 3) = " Web Address Unknown"
GoTo Nrest
End If
DoEvents
XMLPage.Open "GET", site, False
XMLPage.Send
HTMLDoc.body.innerHTML = XMLPage.responseText
Rest.Offset(0, 2) = HTMLDoc.getElementsByTagName("h1")(0).innerText
Set HTMLele = HTMLDoc.getElementsByTagName("div")
If HTMLele(i).innerText = "BreakfastLunchDinner" Then
i = 30
End If
If HTMLele(i).innerText = "English" Then
i = 30
End If
If HTMLele(i).innerText = "BreakfastLunch & Dinner" Then
i = 30
End If
If HTMLele(i).innerText = "" Then
i = 30
End If
Rest.Offset(0, 3) = HTMLele(i).innerText
DoEvents
'
Application.StatusBar = "Updating " & NumofRst & _
" of " & Cntr & " | Started at " & TN & " | Restaurant # " & Rest
NumofRst = NumofRst + 1
Nrest:
Next Rest
Application.DisplayAlerts = True
On Error GoTo 0
SortU
ActiveSheet.Range("L5").Value = ""
Range("TStamp") = Now
Application.Wait (Now + TimeValue("0:00:01"))
Range("A2").Select
Application.Calculation = xlCalculationAutomatic
Call Uptime2
Application.ScreenUpdating = True
Application.StatusBar = False
Application.Wait (Now + TimeValue("0:00:02"))
Range("A2").Select
GoSlow
MsgBox "Update complete!" & vbCr & "Started at " & TN & vbCr & "Ended at " & Now(), , "Good News"
End Sub
Last edited by a moderator:
