VBA macro needed for pulling particular option value of a webpage

Question

I currently have the following macro to load a webpage:

Sub OOS_Query()
'This together with the True value at the end will tell the macro to not update the screen until it reaches a point that I want it to show updates again
Application.ScreenUpdating = False
ActiveWorkbook.Connections("Connection1").Delete
Sheet2.Range("A:C").Clear
With Sheet2.QueryTables.Add(Connection:= _
"URL;http://[ommitted on purpose]id=42908", Destination:=Sheet2.Range("$A$1"))
.FieldNames = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.RefreshPeriod = 5
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True

End With
Application.ScreenUpdating = True
End Sub

As you can see the webpage has an "id" value that is constantly changing as it queries a database. That value looks as follows on the source of the webpage:

Date <select name="id">
<option value='43032' >2017-05-13 05:00:01</option>
<option value='43031' >2017-05-13 04:45:02</option>
<option value='43030' >2017-05-13 04:30:01</option>
<option value='43029' >2017-05-13 04:15:02</option>

...

<option value='43004' >2017-05-12 22:00:01</option>

I am looking for a way to integrate in the code to be able to pull the website with whatever id it has, as long as the time is between 21:58:00 and 22:02:00; for whatever the current date is. The way this is normally done is by accessing the website and selecting out of a drop down menu the date/time that we want to query and then copy pasting the website into the section of the code above.

If I could make it do that automatically, it will remove me having to edit the code everyday.

Thanks in advance!

Forgot to add that the reason for not selecting the time the website shows as in 22:00:01, is because sometimes it changes. — Jahir, May 13 '17 at 10:36
Could you check if that HTML snippet is present in source from XHR which is logged on network tab in browser's developer tools right after you loaded the page (like on [this screenshot](https://i.stack.imgur.com/Tr7Vx.png)). If so it makes possible to retrieve it without using IE. — omegastripes, May 14 '17 at 13:55
@omegastripes It is, I did exactly as your screenshot shows and it does appear in Network>Doc>Response. — Jahir, May 17 '17 at 05:32
Then you may download that HTML source via XHR, and parse each option tag into array via RegEx for further processing and id extraction. — omegastripes, May 17 '17 at 08:01
@omegastripes Pardon my ignorance, but how do I do that via VBA? — Jahir, May 17 '17 at 10:30
Try to extract id and date pairs into array as first step. Take a look at `Sub XmlHttpRequest()` and `Sub ParseToArray()` from [this answer](http://stackoverflow.com/a/28754009/2165759). — omegastripes, May 17 '17 at 19:55
@omegastripes That is too complicated for my level of expertise. But based on what you said I found a way around my problem. — Jahir, May 18 '17 at 08:40

Jahir · Accepted Answer · 2017-05-18T08:50:05.930

I adjusted the code to query the webpage but to extract the ID value from a cell in a sheet of my designation. Then I also added a couple more things on the code.

The ID is easy to figure out since I need it for every day at 10pm (22hr), then I know whatever value is in there is going to be added the number 96. 96 = the amount of times the value changes in a 24 hr period given that they change every 15 mins (4 times in 1 hr). So 4 times 24 gives me 96, which I add to whatever the ID was today at 10pm.

Then I just build 2 columns one with the IDs taking into account what I said above, and the other column with dates. Then I built an array formula on a dummy cell that matches based on the day which gives me the id value im looking for. Code looks like this:

Sub OOS_Query()

Application.ScreenUpdating = False
ActiveWorkbook.Connections("Connection1").Delete
Sheet2.Range("A:C").Clear

Dim wb As Workbook
Dim src As Worksheet
Dim url As String
Dim symbol As String

Set wb = ThisWorkbook
Set src = wb.Sheets("OldTime")
symbol = src.Range("K2")
url = "URL;[omitted on purpose]="
url = url & symbol

With Sheet2.QueryTables.Add(Connection:= _
url, _
Destination:=Sheet2.Range("$A$1"))
.FieldNames = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.RefreshPeriod = 5
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True

End With
Application.ScreenUpdating = True
End Sub

Excell formula:

INDEX(I:I,MATCH(TODAY(),J:J,0))

Hope this helps anyone out there that may have similar questions.

VBA macro needed for pulling particular option value of a webpage

1 Answers1