-
Jan 10th, 2021, 06:06 AM
#1
Thread Starter
New Member
querytable.refresh fails and error handling (alternatives?)
Hello,
Im running into a snag I can't figure out. I was hoping someone could give me a tip or point me in the right direction to resolve my conundrum.
Im running a macro solution in excel to refresh tradingdata from the same site for different assets. These assets are approached in a loop and per loopcycle I am running an add.querytable connecting to a website and pulling data from 'Table 1'. This all works fine for the most part.
Unfortunately, there are some assets for which the tradingdata on the site are in a different Table 'Table 0'. If my macro runs across these assets, the querytable fails on .Refresh BackgroundQuery:=False (error 1004). Logically, as the data is in another table.
The code I am running as the refresh query is as follows;
Code:
ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Bron = Web.Page(Web.Contents(""" & Ticker_link & """))," & Chr(13) & "" & Chr(10) & " Data1 = Bron{1}[Data]," & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"" = Table.TransformColumnTypes(Data1,{{""Date"", type date}, {""Price"", type text}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Vol."", type text}, {""Change %"", Pe" & _
"rcentage.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"""
xlstATD.Activate
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 1"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = Ticker_ID
.Refresh BackgroundQuery:=False
End With
Now what I want to do have VBA check to see if the query was succesfull in pulling data from the site. If not, I want VBA to try a different query (for 'Table 0' and if that fails I want to switch a boolean, so that my script moves on to the next asset (loopcycle).
I've been searching the internet and testing different sollutions, but I don't seem to find a manner in which I can have VBA check if the query failed. I found the .AfterRefresh operation, but I can not grasp how it works, assuming that operation checks to see if the add.querytable was a success.
How can I have VBA check to see if the datadownload from the query was a succes?
thanks in advance for tips and advice.
regards.
-
Jan 10th, 2021, 03:04 PM
#2
Re: querytable.refresh fails and error handling (alternatives?)
Code:
ActiveWorkbook.Queries.Add Name:="Table 1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Bron = Web.Page(Web.Contents(""" & Ticker_link & """))," & Chr(13) & "" & Chr(10) & " Data1 = Bron{1}[Data]," & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"" = Table.TransformColumnTypes(Data1,{{""Date"", type date}, {""Price"", type text}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Vol."", type text}, {""Change %"", Pe" & _
"rcentage.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type gewijzigd"""
xlstATD.Activate
on error goto redo
t = 2
:redo
t = t -1
if t > - 1 then
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table" & t & "";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = Ticker_ID
.Refresh BackgroundQuery:=False
End With
else
mybool = true
end if
this is totally untested and was typed in the browser so may contain typos or code errors
edit: fix an error in code
Last edited by westconn1; Jan 11th, 2021 at 02:53 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|