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

[SteveOranjin]

Hello,

I can't figure out what is going on with this. The Error I get is,

"Run-time error '438': Object Doesn't Support This Property or method." and then it highlights the following.

".ListObject.DisplayName"

I have highlighted it red in the code.

two values to enter into the input box is ,"BRZ", or "ASB"

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
[COLOR=#ff0000]        .ListObject.DisplayName[/COLOR]
    End With
End Sub

 

[SteveOranjin]

I implemented your solution, and the solution of another person through a post I found. They said that 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."

However, I've even added this macro to a new sheet and it still does the same thing. I've also added an if statement that evaluates whether or not the brandcode inserted has the right number of characters attached to it.

Option ExplicitSub Macro3()


    'Defining Variables
    Dim myValue As Variant
    Dim Link    As Variant
    Dim oQuery  As Object
    Dim ws As Worksheet
    Dim l As ListObject
    
    For Each ws In ThisWorkbook.Worksheets
        For Each l In ws.ListObjects
            If l.Name = "brandDataAPI" Then l.Delete
            If l.Parent = "BrandDataAPI" Then l.Delete
            
        Next l
    Next ws


        
        


        '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")
        
        'We are now evaluating to see how long the string was entered. If the string is too long, then we are going to
        'compel the user to enter more.
        
        If Len(myValue) <> 3 Then
            myValue = InputBox("Your brandcode cannot be more or less than 3 characters. Please try again.")
        End If




        '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"""
    
        'Create a ListObjet that holds a Query Table.
        Set oQuery = ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""brandDataAPI"";Extended Properties=""""" _
            , Destination:=Range("$A$1")).QueryTable




        'Execute the query and save the results in the List Object just created.
        With oQuery
            .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
            .Parent.DisplayName = "Brand Data"  'This is the name of the newly added ListObject.
        End With
    
End Sub

 

[SteveOranjin]

Any chance I can get a little more love on this one? Please

Steve