Data table missing intermittently

[silicon_beaver]

I am using VBA code to query html data from a web.
it works fine if step-into (F8). I got whole web contexts and the data table.

But if I hit F5 than F8, I got web contexts but the data table missing.

I did insert, but no improvement.
DoEvents

Any helps would be greatly appreciated.


Code
Sub query_web_data()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")

s = "http://www.uscfinvestments.com/holdings/uso"
Dim htmlText As String
With IE
.Visible = False
.Navigate s
Do Until .ReadyState = 4: DoEvents: Loop
.ExecWB 17, 0 '// SelectAll
Application.Wait Now + TimeValue("00:00:01")
Do Until .ReadyState = 4: DoEvents: Loop
.ExecWB 12, 2 '// Copy selection
.Quit
End With
Workbooks.Add
ActiveSheet.Paste
End Sub



If F8, web contexts and the data table.

Security Quantity Price Market Value
Commodity Interests
NYMEX WTI Crude Oil CL JUL17 55715 49.8 2774607000
US Treasuries
US T BILL ZCP 06/01/17 75000000 99.99 74993802.07
US T BILL ZCP 06/08/17 75000000 99.98 74984624.98
US T BILL ZCP 06/15/17 75000000 99.97 74974468.77
US T BILL ZCP 06/22/17 75000000 99.95 74965333.29
US T BILL ZCP 06/29/17 75000000 99.95 74958750.05
US T BILL ZCP 07/06/17 75000000 99.93 74948749.95
US T BILL ZCP 07/13/17 75000000 99.92 74942718.75
US T BILL ZCP 07/20/17 75000000 99.91 74931937.53
US T BILL ZCP 07/27/17 75000000 99.9 74924385.3
US T BILL ZCP 08/03/17 75000000 99.88 74911458.39
US T BILL ZCP 08/10/17 75000000 99.87 74904687.47
US T BILL ZCP 08/17/17 75000000 99.85 74888958.37
US T BILL ZCP 08/24/17 75000000 99.83 74875770.91
US T BILL ZCP 08/31/17 75000000 99.8 74849000.18
US T BILL ZCP 09/07/17 75000000 99.76 74819749.86
US T BILL ZCP 09/14/17 100000000 99.74 99735388.77
US T BILL ZCP 09/21/17 75000000 99.72 74787937.66
US T BILL ZCP 09/28/17 75000000 99.69 74770083.56
US T BILL ZCP 10/05/17 75000000 99.67 74755739.7
US T BILL ZCP 10/12/17 60000000 99.65 59789550.16
US T BILL ZCP 10/19/17 75000000 99.64 74726614.45
US T BILL ZCP 10/26/17 90000000 99.6 89638999.83
US T BILL ZCP 11/02/17 75000000 99.57 74680343.82
US T BILL ZCP 11/09/17 80000000 99.54 79629266.91
US T BILL ZCP 11/16/17 75000000 99.53 74646791.98
US T BILL ZCP 11/24/17 80000000 99.47 79579678.05
Share Prices
Cash
FIDELITY GOVERNMENT PORT-INS 300000000 1 300000000
GOLDMAN SACHS FIN SQ GOVT-FS 160000000 1 160000000
MORGAN STANLEY LIQ GOVT-INST 150000000 1 150000000
Interest Receivable 278907 1 278907.45
US DOLLARS 221771204 1 221771204
< Swipe table for more info >
Pending Trades as of 05/26/2017
Security Action Quantity Price
Commodity Interests
NYMEX WTI Crude Oil CL JUL17 Sell -1239 49.8
US Treasuries
Share Prices
Cash
Creation / Redemption as of 05/26/2017
Shares Created / Redeemed
-6000000
Back to Top

 

[silicon_beaver]

btw,
I first tried the below code. No data table returned at all.
Can anyone tells me what I did wrong? Thanks,


Sub query_web_data()

Dim qurl As String

qurl = "http://www.uscfinvestments.com/holdings/uso"
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("A1"))
.PostText = True
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

 

[silicon_beaver]

Update

For the ie object method, I made it working using 4 sec wait in total (If <4 sec, the data table missing). it runs fine, just too slow.
Any improvement I can do? Thanks in advance.

Code


Sub query_web_data()
Dim IE As Object
Dim qurl As String

Set IE = CreateObject("InternetExplorer.Application")
qurl = "http://www.uscfinvestments.com/holdings/uso"
wait0 = 2
With IE
.Visible = False
.Navigate qurl
' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", wait0, Now)
Loop
.ExecWB 17, 0 '// SelectAll
' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", wait0, Now)
Loop
.ExecWB 12, 2 '// Copy selection
.Quit
End With
Workbooks.Add
ActiveSheet.Paste
End Sub



But I still cannot make the ActiveSheet.QueryTables method working. The data table always missing in this way.

 

[silicon_beaver]

Update

For .QueryTables method, I have read this thread by John_w
https://www.mrexcel.com/forum/excel-questions/424227-web-query-fails-returned-no-data.html

It's confirmed, 'no-store, no-cache',
So, it's no-go. good to learn.
I have to stick with the IE object method. Even it's slow.