VBA: Pass a link encoded into a variable INTO a web pull

[SteveOranjin]

Hello,

I'm having an issue with passing a variable as a link into a web pull. When I hardcode the link, it makes the necessary pull and returns the data. The problem is, as soon as I put the link into a variable, it rejects the same pull. Even if the variable is the same as the hard coded text entry.

Sub Macro3()
'
' Macro3 Macro
'


On Error Resume Next
ActiveWorkbook.Queries("brandDataAPI2 (2)").Delete
On Error GoTo 0


    ActiveWorkbook.Queries.Add Name:="brandDataAPI2 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = [I][B]Csv.Document(Web.Contents(""http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI2.cfm?brandCode=BRZ"")[/B][/I],[Delimiter="","", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""BrandName""" & _
        ", type text}, {"" BrandCode"", type text}, {"" BrandID"", Int64.Type}, {"" datalastUpdate"", type date}, {"" numproducts"", Int64.Type}, {""priceMethod"", type text}, {""URL"", type text}, {""showPrice"", Int64.Type}, {""MAP_YN"", Int64.Type}, {""MAP"", type text}, {""msrpNotes"", type text}})" & 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=""brandDataAPI2 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [brandDataAPI2 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "brandDataAPI2__2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

When I run the following code, below, though, it does not work. (please note, the differences between each are bolded and underlined for your convenience.

Sub Macro3()
'
' Macro3 Macro
'
Dim mpsLink As String


On Error Resume Next
ActiveWorkbook.Queries("brandDataAPI2 (2)").Delete
On Error GoTo 0


[U][I][B]mpsLink = "http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI2.cfm?brandCode=BRZ"[/B][/I][/U]


    ActiveWorkbook.Queries.Add Name:="brandDataAPI2 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = [U][I][B]Csv.Document(Web.Contents(" & mpsLink & ")[/B][/I][/U],[Delimiter="","", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""BrandName""" & _
        ", type text}, {"" BrandCode"", type text}, {"" BrandID"", Int64.Type}, {"" datalastUpdate"", type date}, {"" numproducts"", Int64.Type}, {""priceMethod"", type text}, {""URL"", type text}, {""showPrice"", Int64.Type}, {""MAP_YN"", Int64.Type}, {""MAP"", type text}, {""msrpNotes"", type text}})" & 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=""brandDataAPI2 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [brandDataAPI2 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "brandDataAPI2__2"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[smozgur]

Try changing the variable to include double quotes as the source requires that way:

mpsLink = """http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI2.cfm?brandCode=BRZ"""

Suat

 

[SteveOranjin]

Try changing the variable to include double quotes as the source requires that way:

mpsLink = """http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI2.cfm?brandCode=BRZ"""

Suat

Hey man. You saved the day. Thanks

'
' Macro3 Macro
'


Dim mpsLink As String
Dim brandCode As String


On Error Resume Next
ActiveWorkbook.Queries("brandDataAPI2 (2)").Delete
On Error GoTo 0


brandCode = InputBox("Enter your BrandCode here")
Do While Len(brandCode) <> 3
    brandCode = InputBox("Brandcodes are only 3 letters. Please Try Again.")
Loop
    
mpsLink = """http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI2.cfm?brandCode=" & brandCode & """"


    Application.CutCopyMode = False ' the clause we added.
    ActiveWorkbook.Queries.Add Name:="brandDataAPI2 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(" & mpsLink & "),[Delimiter="","", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""BrandName""" & _
        ", type text}, {"" BrandCode"", type text}, {"" BrandID"", Int64.Type}, {"" datalastUpdate"", type date}, {"" numproducts"", Int64.Type}, {""priceMethod"", type text}, {""URL"", type text}, {""showPrice"", Int64.Type}, {""MAP_YN"", Int64.Type}, {""MAP"", type text}, {""msrpNotes"", type text}})" & 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=""brandDataAPI2 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [brandDataAPI2 (2)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "brandDataAPI2__2"
        .Refresh BackgroundQuery:=False
    End With
    
    Dim brandName As String
    brandName = Range("A2").Value
    
    MsgBox brandName
    
End Sub