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]

By the way, I'm trying to import a table here. I'm importing it from the internet.

I want to do this so I can have my VBA grab and populate fields directly from the source.

Steve

 

[JLGWhiz]

It looks like the .ListObject is redundant to the ListObjects.Add, so I would try removing .ListObject and just make that line

.DisplayName

 

[JLGWhiz]

If that does not work, you will probably have to do that as a separate bit of code. It does not show as a property of the ListObjects.Add method.

 

[SteveOranjin]

If that does not work, you will probably have to do that as a separate bit of code. It does not show as a property of the ListObjects.Add method.

Can you walk me through how to do that? I don't know how to do that.

What that guy recommended to me did not work.

Steve

 

[hotabae]

If that does not work, you will probably have to do that as a separate bit of code. It does not show as a property of the ListObjects.Add method.

It shows as a property if you include the query table bit (shorthand below):

ListObjects.Add.QueryTable.ListObject.DisplayName

What are you trying to do with the display name string?

Are you trying to assign it somewhere, or change it? That property is just a string, not an action.

 

[SteveOranjin]

I'm trying to pull the CSV file in as a table.

Go to that link. You'll see it pulls in a CSV file. I want to import that file into a sheet.


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

 

[SteveOranjin]

Can I get a little love here?

 

[Leith Ross]

Hello Steve,

The property DisplayName applies to specific ListObject in the ListObjects collection. You have created a List Object that contains a Query Table. To return or change the name of List Object, you must reference the parent object of the Query Table. In this case, the parent object of the Query Table is the List Object.

Sub Macro3()


    'Defining Variables
    Dim myValue As Variant
    Dim Link    As Variant
    Dim oQuery  As Object


        '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"""
    
        'Create a ListObjet that holds a Query Table.
        Set oQuery = ActiveSheet.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]

That doesn't work. It returns the same error.

and code you can enter is "BRZ"