I have data in sheet 1. Normally I go to power query and do my transformations, then close, and load to an existing sheet 2.
I would like to automate this using VBA, where I can just run my power query automatically and populate the transformation to sheet 2.
Macro recorder doesn't seem to allow me to record the steps. And there isn't much online about doing this.
Trying some simpler code:
Sub LoadToWorksheetOnly() 'Sub LoadToWorksheetOnly(query As WorkbookQuery, currentSheet As Worksheet) ' The usual VBA code to create ListObject with a Query Table ' The interface is not new, but looks how simple is the conneciton string of Power Query: ' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name query = Sheets("Sheet6").Range("A1").value 'here is where my query from power query is. I put the text from power query avanced editor in another sheet cell. currentSheet = ActiveSheet.Name With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name _ , Destination:=Sheets("target").Range("$A$1")).QueryTable .CommandType = xlCmdDefault .CommandText = Array("SELECT * FROM [" & query.Name & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = False .Refresh BackgroundQuery:=False End With End Sub Here is my issue when trying to load to new sheet manually.
1 Answers
Answers 1
WIP:
So how to write this to be sufficient? The bottom line is you should set your query up using the inbuilt tools. Not VBA. You load your data via the appropriate method which can be from file, looping files in a folder, web, database.... the list goes on. You can import from external sources as well as load from internal. Have a look here for more information on loading from external sources.
Once you have secured your source and it is loaded you will be presented with the query editor where you can perform your transformation steps.
The point being that as you perform your steps using the UI, M code is written in the background and forms the basis of a re-usable query provided you don't change the source format or location.
In your case, when you have performed your steps and have a query as you wish you then close and load to sheet2.
At this step, the first time you are setting this up you will select sheet 2 as your close and load destination:
NB: When you select existing sheet, ensure Sheet 2 already exists and you can manually edit Sheet2! in front of the suggested range.
You are experiencing issues because you keep trying to recreate all of this with code. Don't. Set it up using the UI and load to sheet2. From then on, either open the query editor to edit the steps and/or refresh the query to load the existing sheet2 with new/refreshed data.
Some of the available methods for refreshing your query:
The query will be refreshed by VBA/Manual refreshes to the sheet it resides in (Sheet2), or to the workbook itself e.g. Sheet2.Calculate , ThisWorkbook.RefreshAll, manually pressing the refresh workbook button in the data tab (these are all overkill really)
More targeted methods:
VBA for the query table in sheet 2:
ThisWorkbook.Worksheets("Sheet2").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False Change the above to the appropriate table etc.
Right clicking in the querytable itself and selecting refresh:
Click on the refresh button in the workbook queries window on the right hand side for the query in question (icon with green circling arrows)
The Ken Pulls VBA way (minor edit from me)
Option Explicit Public Sub UpdatePowerQueries() ' Macro to update my Power Query script(s) Dim lTest As Long, cn As WorkbookConnection On Error Resume Next For Each cn In ThisWorkbook.Connections lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare) If Err.Number <> 0 Then Err.Clear Exit For End If If lTest > 0 Then cn.Refresh Next cn On Error GoTo 0 End Sub There shouldn't be any real need for you to doing all of this work via VBA. You may have some tricky data manipulation you feel more comfortable doing with VBA and then having powerquery access that processed data as source. You can fire of the whole lot by having a sub that call the processing routine and then uses one of the vba command methods listed above. There are more methods and I will add them when I have more time.
Calculations:
If you have calculations that depend on the powerquery output you have 4 obvious immediate options:
- Add these calculations where possible into powerquery. It supports calculated columns, user defined functions and lots more.
- Add the powerquery output to the data model and use the data model to perform calculations including calculated fields. This will give you access to time intelligence functions aswell.
- Use VBA to add the calculations to the appropriate areas in sheet 2 if the range changes on refresh
- If range doesn't change on refresh simply put your formulas out of the way.
0 comments:
Post a Comment