VBA: Run-time error '438': Object doesn't support this property or method

Question

I'm trying to import a web based CSV file dynamically through changing the URL each time I run it. So I've got a URL that I'm dynamically managing by entering the last string as a variable that the user enters through an input box.

The problem is, that I'm getting a "Run-time error '438': Object doesn't support this method.

So this is an example of the URL that is being put through.You can change "BRZ" to "ASB" to see a different example if you wish.

http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI.cfm?brandCode=BRZ

As you can see, that link will take you right to the folder.

I have no idea what it means. I see that a lot of people are having the same problem, but the ".ListObject.DisplayName" is the portion of the code that is returning the error. I have the portion of the code underlined and highlighted for ease of reference.

Sub Macro3()
'Defining Variables
Dim myValue As Variant
Dim Link As Variant


'Creating a link
Link = "http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI.cfm?brandCode="


'creating a the variable to be passed into the link
myValue = InputBox("Enter your brand code here")


'completing our link
Link = "http://cf3.myplumbingshowroom.com/scheduledScripts/brandDataAPI.cfm?brandCode=" & myValue
'checking if it exists
MsgBox Link


'if the query already exists, we're going to delete it. Otherwise, we're going to ignore that function.


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




    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="brandDataAPI", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(" & Link & "),[Delimiter="","", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Header" & _
        "s"",{{""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"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""brandDataAPI"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [brandDataAPI]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .Refresh BackgroundQuery:=True
        ***.ListObject.DisplayName***
    End With
End Sub

Answer 1

This happens if you already have a table by this name. Particularly if you had a table by this name and you deleted it but it hasn't quite gone away despite not being visible.

So loop all the listobjects first and delete any with the proposed name e.g. "Hello" :

Option Explicit

Sub test()
    Dim ws As Worksheet
    Dim l As ListObject
    For Each ws In ThisWorkbook.Worksheets
        For Each l In ws.ListObjects
            If l.Name = "Hello" Then l.Delete
        Next l
    Next ws

'Do your stuff
End Sub