martinequilibrium
New Member
- Joined
- Feb 26, 2016
- Messages
- 13
Hi lovely People!
I'm trying to update my power pivot data model to keep a table with my measures but change a Power Pivot Data Connection to a Power Query Data Connection.
Using Power Pivot Data Model Connections in my WorkbookDataModel.xlsx
This is my table:
When trying to edit to get the query:
If i try to put the same workbook to "get" the query connection from the active workbook WorkbookDataModel.xlsx it tells me of course that it is open and It cannot connect to itlsef.
Instead of the external workbook i want to use a query from WorkbookDataModel (from the same file that has the data model)
Trying to load it to the data model to guess how it connects:
This of course creates a new Table because DetalleViajes already exists.
Inspecting the properties
VBA Method
Using the VBA Macro recorder with the existing connections tab i got these vba snippets:
When I try the VBA route
I get:
run time error 1004 application defined or object defined error
¿How con i preseve my measures and pivot tables but change the source?
The columns are the same of course in the external table and power query since the power query reads this external table too.
Thanks Everyone!
PS: My Excel Version is 2016.
I'm trying to update my power pivot data model to keep a table with my measures but change a Power Pivot Data Connection to a Power Query Data Connection.
Using Power Pivot Data Model Connections in my WorkbookDataModel.xlsx
This is my table:
When trying to edit to get the query:
If i try to put the same workbook to "get" the query connection from the active workbook WorkbookDataModel.xlsx it tells me of course that it is open and It cannot connect to itlsef.
Instead of the external workbook i want to use a query from WorkbookDataModel (from the same file that has the data model)
Trying to load it to the data model to guess how it connects:
This of course creates a new Table because DetalleViajes already exists.
Inspecting the properties
VBA Method
Using the VBA Macro recorder with the existing connections tab i got these vba snippets:
VBA Code:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\Users\martin\Downloads\directory\filename.xls;Persist Security Info=False;Extended Properties="Excel 12.0;HDR=Yes"
VBA Code:
Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DetalleViajes;Extended Properties=
When I try the VBA route
VBA Code:
Sub ChangeExcelExternalToPowerQuery()
'
' ChangeExcelExternalToPowerQuery Macro
'
'
With ActiveWorkbook.Connections("Excel detalle-paradas-julio").OLEDBConnection
.BackgroundQuery = False
.CommandType = xlCmdTableCollection
.Connection = _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=DetalleViajes;Extended Properties="
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("Excel detalle-paradas-julio")
.Name = "Excel detalle-paradas-julio"
.Description = ""
End With
End Sub
I get:
run time error 1004 application defined or object defined error
¿How con i preseve my measures and pivot tables but change the source?
The columns are the same of course in the external table and power query since the power query reads this external table too.
Thanks Everyone!
PS: My Excel Version is 2016.