Macro Recorded while Executing PowerQuery: unexpected result of VBA code

Question

I used PowerQuery to store 4 queries as connections only, and later on combine-append them into two tables. This action was recorded into a macro so I could use it to repeat the same process in the future. I attach here the relevant part of the VBA code generated by the Macro recorded. I found two issues that I didn't not expect and would like to find a solution for.

Firstly, the workbook is invoked by its actual, full file name, instead of something relative such as "currentworkbook" or similar (see second line)

    Workbooks( _
    "RP-DM_Survey123_Formatting_Template_March_2021_Copy4_PowerQuery.xlsm"). _
    Connections.Add2 "Query - Trees_Query", _
    "Connection to the 'Trees_Query' query in the workbook.", _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Trees_Query;Extended Properties=""""" _
    , "SELECT * FROM [Trees_Query]", 2

Secondly, the tables generated by the queries are stored into new worksheets which I had named during the macro recording (in this example, the name is 'PruningQuery'), and I expected the naming to be carried on the next time I run the macro. Instead, the tables created go into new worksheets with name 'Sheet6', 'Sheet7' or whatever number follows from the last sheet created in that workbook.

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

Any help or light shed on these two issues would be massively appreciated. Thanks :)

Issue 1: ActiveWorkbook? Even better, a workbook variable: Dim wb As Workbook, Set wb = ActiveWorkbook, or Dim wb As Workbook, Set wb = Workbooks.Open(filename). — BigBen

rafadepedro rafadepedro · Accepted Answer · 2021-03-16T09:37:15

Thank you @BigBen. I changed the line for ThisWorkbook.Connections.Add2 "Query..." etc. and it worked well.

Macro Recorded while Executing PowerQuery: unexpected result of VBA code

1 Answers