(Solved) – Web.Contents() Parameter String Not Parsing

(solved)-–-web.contents()-parameter-string-not-parsing

Background:

I need to retrieve the values from various Web tables with dynamic URLs.

Problem:

The built in function works but only if the URL is written specifically and it does not parse variable strings into it.

Code:

Sub Exec_AddCurrencyDataTable(WBToAddIn As Workbook, TxtSheetNameAs As String, TxtWebAddress As String, TxtTableNameAs As String)
    With WBToAddIn
    .Queries.Add Name:="Table 0", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(TxtWebAddress))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Units per USD"", type number}, {""USD per Unit"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    'The previous code does not work since it parses the variable name itself and not the value from it. This works: Source = Web.Page(Web.Contents(""https://www.xe.com/currencytables/?from=USD&date=2020-03-17""))
    .Worksheets.Add
    .ActiveSheet.Name = TxtSheetNameAs
    With .Sheets(TxtSheetNameAs).ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql: .CommandText = Array("SELECT * FROM [Table 0]"): .RowNumbers = False: .FillAdjacentFormulas = False: .PreserveFormatting = True: .RefreshOnFileOpen = False: .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells: .SavePassword = False: .SaveData = True: .AdjustColumnWidth = True: .RefreshPeriod = 0: .PreserveColumnInfo = True: .ListObject.DisplayName = "Table_0": .Refresh BackgroundQuery:=False: End With
    .Sheets(TxtSheetNameAs).ListObjects("Table_0").Name = TxtTableNameAs
    End With
End Sub

I have not set error handling (Such as query existing) yet, before that, I would need to solve the URL error.

Further thoughts

I have read the following Web.Contents() update URL in a for loop VBA, however, I would rather to know how could I correctly use the Web.Contents as I intend to.

Leave a Reply

Your email address will not be published. Required fields are marked *