Applying a formula to Get From Web URL with VBA Macro

I'm trying to find a way to apply a formula to a get data from web url.

So the url will look like:

http://www.thisurl.com/formula/restOfTheUrl

I'm new to this, so I tried recording the macro using the Advanced Get From Web function and breaking out the elements of the URL from there and substituting the formula in.

So the section of the code looked like this

Source = Xml.Tables(Web.Contents(""http://www.thisurl.com/"" & sum(1,2,3) & ""/restOfTheUrl""))

If the code worked as expected, the url output would've been:

http://www.thisurl.com/6/restOfTheUrl

The error I'm getting is:

Run-time error '1004': Application-defined or object-defined error

I'm using the sum formula just for proof of concept & testing purposes.

The beginning of the macro that was recorded that leads into the formula-URL issue is:

ActiveWorkbook.Queries.Add Name:="item", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Xml.Tables(Web.Contents(""http://www.

I found this question being asked which is similar to what I am asking, but it is from 2002 and their answer doesn't line up with the output I'm seeing from the recorded macro.

https://www.mrexcel.com/forum/excel-questions/11732-perform-web-query-url-cell.html

Appreciate the response!

At the moment, I don't think this is the solution, so let me give some more details here. Part of the problemI think is definitely quotation mark usage since the URL I'm working with contains quotation marks.

First, I recorded a macro using Get Data From Web and the Basic option.

The URL I used was basically: http://www.thisurl.com/abc/subject=["word1","word2"]&output=xml

Recorded Marco Output:

Sub Macro1()
'
' Macro2 Macro
'

'
    ActiveWorkbook.Queries.Add Name:="item", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Xml.Tables(Web.Contents(""http://www.thisurl.com/abc/subject=[""""word1"""",""""word2""""]&output=xml""))," & Chr(13) & "" & Chr(10) & "    Table0 = Source{0}[Table]," & Chr(13) & "" & Chr(10) & "    Table1 = Table0{0}[Table]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table1,{{""Attribute:string"", type text}, {" & _
        """Attribute:volume"", Int64.Type}, {""Attribute:m1"", Int64.Type}, {""Attribute:m1_month"", Int64.Type}, {""Attribute:m1_year"", Int64.Type}, {""Attribute:m2"", Int64.Type}, {""Attribute:m2_month"", Int64.Type}, {""Attribute:m2_year"", Int64.Type}, {""Attribute:m3"", Int64.Type}, {""Attribute:m3_month"", Int64.Type}, {""Attribute:m3_year"", Int64.Type}, {""Attribute" & _
        ":m4"", Int64.Type}, {""Attribute:m4_month"", Int64.Type}, {""Attribute:m4_year"", Int64.Type}, {""Attribute:m5"", Int64.Type}, {""Attribute:m5_month"", Int64.Type}, {""Attribute:m5_year"", Int64.Type}, {""Attribute:m6"", Int64.Type}, {""Attribute:m6_month"", Int64.Type}, {""Attribute:m6_year"", Int64.Type}, {""Attribute:m7"", Int64.Type}, {""Attribute:m7_month"", In" & _
        "t64.Type}, {""Attribute:m7_year"", Int64.Type}, {""Attribute:m8"", Int64.Type}, {""Attribute:m8_month"", Int64.Type}, {""Attribute:m8_year"", Int64.Type}, {""Attribute:m9"", Int64.Type}, {""Attribute:m9_month"", Int64.Type}, {""Attribute:m9_year"", Int64.Type}, {""Attribute:m10"", Int64.Type}, {""Attribute:m10_month"", Int64.Type}, {""Attribute:m10_year"", Int64.Typ" & _
        "e}, {""Attribute:m11"", Int64.Type}, {""Attribute:m11_month"", Int64.Type}, {""Attribute:m11_year"", Int64.Type}, {""Attribute:m12"", Int64.Type}, {""Attribute:m12_month"", Int64.Type}, {""Attribute:m12_year"", Int64.Type}, {""Attribute:cpc"", type number}, {""Attribute:cmp"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=item;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [item]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "item"
        .Refresh BackgroundQuery:=False
    End With
End Sub

This macro will work and give me the output I need.

Then I tried recording the Get Data From Web and with the Advanced option breaking the URL up into 3 parameters.

Param1: http://www.thisurl.com/abc/subject=["
Param2: word1
Param3: "]&output=xml

Recorded Macro Output:

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveWorkbook.Queries.Add Name:="item", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Xml.Tables(Web.Contents(""http://www.thisurl.com/abc/subject=["""""" & ""word1"" & """"""]&output=xml""))," & Chr(13) & "" & Chr(10) & "    Table0 = Source{0}[Table]," & Chr(13) & "" & Chr(10) & "    Table1 = Table0{0}[Table]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table1,{{""Attribute:string"", type text}, {""At" & _
        "tribute:volume"", Int64.Type}, {""Attribute:m1"", Int64.Type}, {""Attribute:m1_month"", Int64.Type}, {""Attribute:m1_year"", Int64.Type}, {""Attribute:m2"", Int64.Type}, {""Attribute:m2_month"", Int64.Type}, {""Attribute:m2_year"", Int64.Type}, {""Attribute:m3"", Int64.Type}, {""Attribute:m3_month"", Int64.Type}, {""Attribute:m3_year"", Int64.Type}, {""Attribute:m4" & _
        """, Int64.Type}, {""Attribute:m4_month"", Int64.Type}, {""Attribute:m4_year"", Int64.Type}, {""Attribute:m5"", Int64.Type}, {""Attribute:m5_month"", Int64.Type}, {""Attribute:m5_year"", Int64.Type}, {""Attribute:m6"", Int64.Type}, {""Attribute:m6_month"", Int64.Type}, {""Attribute:m6_year"", Int64.Type}, {""Attribute:m7"", Int64.Type}, {""Attribute:m7_month"", Int64" & _
        ".Type}, {""Attribute:m7_year"", Int64.Type}, {""Attribute:m8"", Int64.Type}, {""Attribute:m8_month"", Int64.Type}, {""Attribute:m8_year"", Int64.Type}, {""Attribute:m9"", Int64.Type}, {""Attribute:m9_month"", Int64.Type}, {""Attribute:m9_year"", Int64.Type}, {""Attribute:m10"", Int64.Type}, {""Attribute:m10_month"", Int64.Type}, {""Attribute:m10_year"", Int64.Type}," & _
        " {""Attribute:m11"", Int64.Type}, {""Attribute:m11_month"", Int64.Type}, {""Attribute:m11_year"", Int64.Type}, {""Attribute:m12"", Int64.Type}, {""Attribute:m12_month"", Int64.Type}, {""Attribute:m12_year"", Int64.Type}, {""Attribute:cpc"", type number}, {""Attribute:cmp"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=item;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [item]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "item"
        .Refresh BackgroundQuery:=False
    End With
End Sub

This macro works so long as ""word1"" is a static string of text between two quotation marks.

I just tried subbing ""word1"" with application.Sum(1,2,3) and got the same error.

The end goal here is to be able to control what goes in the third parameter based on other data. I already have a function set up that should do that. Right now I'm just trying to find a way to run any function in the middle of a URL if that is possible.