Recording Power Query actions

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,353
I have a workbook called Data.xlsx that contains a single column of numerical data, numbers 1 to 10 in cells A1 through to A10.

In another workbook, I start by recording a macro, taking these steps:

1. Clicked Data
2. Get Data
3. From File
4. From Workbook
5. Chose the file, Data.xlsx and clicked Import
6. Selected the worksheet (Sheet1) containing the data
7. Clicked Transform Data
8. Clicked the filter dropdown and selected greater than 3
9. Clicked OK and Close and Load

Stopped the macro recording button.

This is the code generated:

Code:
Option Explicit

Sub Macro1()

    Range("A1").Select
    ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\MyFolder\data.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Sheet1_Sheet,{{""Column1"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each [Column1] > 3)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Sheet1"
        .Refresh BackgroundQuery:=False
    End With
End Sub

However when I try to run the above code, I get an error message:

Code:
Run-time error

A query with the name 'Sheet1' already exists

What does it mean? Is it refering to the workbook Data.xlsx or the workbook which I am recording the macro? I tried changing the name of the worksheet in both workbooks (from Sheet1 to Sheet10) but the error remains.

Thanks
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,647
I've never tried to record a macro of a Power Query -query. To me, the choice is usually either a macro or a Power Query.

Looks like the macro starts with a line that creates a new query called "Sheet1". If you recorded the macro looks like you forgot to delete the existing query. The error says there's already a query by that name. What if you tried just refreshing the existing query (Data => Refresh All)? Maybe you don't even need the macro? But if you do, change the query name into a variable (something with current date and/or time should be a good one because it's unique every time you run it) and see if your code works better.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,353
I've never tried to record a macro of a Power Query -query. To me, the choice is usually either a macro or a Power Query.

Looks like the macro starts with a line that creates a new query called "Sheet1". If you recorded the macro looks like you forgot to delete the existing query. The error says there's already a query by that name. What if you tried just refreshing the existing query (Data => Refresh All)? Maybe you don't even need the macro? But if you do, change the query name into a variable (something with current date and/or time should be a good one because it's unique every time you run it) and see if your code works better.

Thanks

The reason I recorded a macro that mimics my PQ actions is I want to know if it's possible to automate PQ.

Before PQ came along, I was writing VBA to manipulate data and the user could click a button to do that.

Now most of the manipulating is done using PQ, I suppose one would do that manually first, then write the VBA code to refresh.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,012
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Now most of the manipulating is done using PQ, I suppose one would do that manually first, then write the VBA code to refresh.

This is exactly what I do.

This can be run from a command button if desired.

VBA Code:
Sub Macro4()
    ActiveWorkbook.RefreshAll
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,896
Members
412,752
Latest member
LUIS SAMANO
Top