Automate Power Query with VBA

[dreid1011]

I am trying to run the following VBA code, but I am running into an Application Defined/Object Defined Error. I recorded my actions to set up the query, and now I am just trying to change the table name to a variable and that seems to be giving me trouble.

Error on this line of the code below:

 With ws.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & tName & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable

Private Sub FortePaymentFile()
Dim wb As Workbook
Dim ws As Worksheet
Dim dataRng As Range
Dim lRow As Long
Dim ffXex, tName As String

Set wb = ActiveWorkbook
Set ws = ActiveSheet


lRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set dataRng = ws.Range("A1:F" & lRow)

tName = "Table" & ws.ListObjects().Count + 1

    dataRng.Select
    Application.CutCopyMode = False
    ws.ListObjects.Add(xlSrcRange, dataRng, , xlYes).Name = tName
    Range(tName & "[#All]").Select
    wb.Queries.Add Name:=tName, Formula:="let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""" & tName & """]} _
[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""TA_ID"", Int64.Type}, {""OWNER"", type text} _
, {""TAX_YEAR"", Int64.Type}, {""TOTAL_DUE"", type number}, {""SUPPRESS_PAYMENT"", type text}, {""X"", type text}})," & Chr(13) & "" & Chr(10) & _
"    #""Merged Columns"" = Table.CombineColumns(#""Changed Type"",{""SUPP" & _
        "RESS_PAYMENT"", ""X""},Combiner.CombineTextByDelimiter("""", QuoteStyle.None),""SX"")," & Chr(13) & "" & Chr(10) & _
"    #""Grouped Rows"" = Table.Group(#""Merged Columns"", {""TA_ID"", ""OWNER"", ""SX""}, {{""TOTAL_DUE"", each List.Sum([TOTAL_DUE]), _
type nullable number}})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Grouped Rows"", each ([SX] <> ""X""))" _
& Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""
    wb.Worksheets.Add
    With ws.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & tName & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & tName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = tName
        .Refresh BackgroundQuery:=False
    End With
End Sub

I have a feeling it has to do with the "" but I am not sure where. I tried adding/removing some, but that didn't seem to help.

 

[dreid1011]

It was indeed NOT the "". It did not like "ws" instead of ActiveSheet. Anyways, now the error is further down on this line:

.ListObject.DisplayName = tName

And when I look at the table name in the top left corner of the ribbon, it is completely renamed from what I was expecting it to be.

 

[dreid1011]

Problem solved. Commented out the line, but I see where it was renaming the table and how/why. Just need to add some code to adjust the table name variable or add a second variable to house the secondary name.