VBA Web Query from Cell

Question

I'm a beginner at VBA, but what I'm trying to do is pull data using the "From Web" tool in Excel, but from a designated cell in my worksheet. So I have 2 different codes below. The first one works but it has the actual link in the Source = Csv.Document(Web.Contents(""ftp://ftp.hp.com/pub/softpaq/sp82501-83000/sp82564.cva"").... line. Running this macro works fine.

However I want to get that link from a cell instead of manually entering the link so I changed it up a little in the second block of code. So the only thing that I really change in that I made a variable that stores the cell value that contains the link I want, and then uses that variable in Web.Contents(). However, this throws up an error

(Run-time error '1004': Application-defined or object-defined error).

Using the debug tool shows that it stops at the .Refresh BackgroundQuery:=False line.

I'm not sure what the issue is since all I'm doing is using the same link but using a variable holding the link instead of the actual link itself?

Sub query()

    ActiveWorkbook.Queries.Add Name:="sp85090", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(""ftp://ftp.hp.com/pub/softpaq/sp82501-83000/sp82564.cva""),[Delimiter=""#(tab)"", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", 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=sp85090;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [sp85090]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        ' .ListObject.DisplayName = "_sp85090_2"
        .Refresh BackgroundQuery:=False
    End With

    ActiveWorkbook.Queries("sp85090").Delete
    ActiveWorkbook.Connections("Connection").Delete
    Application.CommandBars("Queries and Connections").Visible = False
End Sub


Sub query()

    softpaqLink = Sheets("test").Cells(3, "H").Value

    ActiveWorkbook.Queries.Add Name:="sp85090", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(""softpaqLink""),[Delimiter=""#(tab)"", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", 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=sp85090;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [sp85090]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        ' .ListObject.DisplayName = "_sp85090_2"
        .Refresh BackgroundQuery:=False
    End With

    ActiveWorkbook.Queries("sp85090").Delete
    ActiveWorkbook.Connections("Connection").Delete
    Application.CommandBars("Queries and Connections").Visible = False
End Sub

Answer 1

• Make sure Sheet with name test has the URL
• Always declare a variable - defined softpaqLink as a string which stores the path
• ""softpaqLink"" Should be """ & softpaqLink & """

• Use Thisworkbook instead of ActiveWorkbook

---

Sub query1()

    Dim softpaqLink  As String
    softpaqLink = Sheets("test").Cells(3, "H").Value

    ThisWorkbook.Queries.Add Name:="sp85090", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(""" & softpaqLink & """),[Delimiter=""#(tab)"", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ThisWorkbook.Worksheets.Add

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=sp85090;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [sp85090]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        ' .ListObject.DisplayName = "_sp85090_2"
        .Refresh BackgroundQuery:=False
    End With

    ThisWorkbook.Queries("sp85090").Delete
    ThisWorkbook.Connections("Connection").Delete
    Application.CommandBars("Queries and Connections").Visible = False
End Sub