SteveOranjin
Board Regular
- Joined
- Dec 18, 2017
- Messages
- 170
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.
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.
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.
Code:
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.
Code:
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