Michael has attended:
Excel PowerPivot course
Powerpivot
Hi,
I am looking to create a powerpivots through a macro.
My approach was to record the macro first. I wanted it to:
Save the file with the data in it
Open a new workbook
Save new work book with appropriate name
Open powerpivot window
Get external data from other sources>excel>closed file
Save file
However recording the macro produces the following VBA code relating to the PPivot
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"H:\Wholesale General\Model Build\Strat_Model_1.0_PP_Database.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Windows("Strat_Model_1.0.xlsm").Activate
ActiveWorkbook.Save
ActiveWindow.Close
Workbooks("Strat_Model_1.0_PP_Database.xlsx").Connections.AddFromFile
So it looks to me that PPIvot won't create with VBA?
If it does could you walk me through
Thanks
Mike
RE: Powerpivot
Hi Mike,
Thank you for the forum question.
Microsoft has not developed the objects to work with VBA in the PowerPivot window. Well they have introduced few objects in Excel 2016. You can manipulate PowerPivot PivotTables from VBA but not as you want record, that you create a data model.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Powerpivot
Thanks Jens
Would I be bale to write VBA code that automatically produces cube functions in cells?
i.e. if i have a spreadsheet with the PPivot database already created could I run code that would just input the predefined formulas? The table will already be linked if thats the case I believe?
Thanks
Mike
RE: Powerpivot
Hi Mike,
Yes you can write VBA code to create Cube functions.
Sub CreateCubeFunctions()
ActiveCell.FormulaR1C1 = _
"=CUBESET(""ThisWorkbookDataModel"",""[Customers].[Store Name].children"",""Clients"")"
End Sub
The code above will insert the Cubeset function in the active cell. You can use the macro recorder to record the code. If you have experience working with ADO (ActiveX Data Object) you can write your own user defined functions.
You can see an example on Microsoft's website (the link below).
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/97a63c4f-cbc1-486f-89eb-39a8a3a43b43/cube-value-function-in-vba?forum=sqlanalysisservices
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Powerpivot
Thanks Jens, this was my plan indeed. But i am not sure what the ADO refers to.
My plan was to use cells as references e.g.
"=CUBESET(""ThisWorkbookDataModel"",""[Cell A].[Cell B].children"",""Clients"")" etc...
RE: Powerpivot
Hi Mike,
Please have a look at the code below.
ActiveCell.FormulaR1C1 = _
"=CUBESET(""ThisWorkbookDataModel"",""[""&R4C5&""].[""&R5C5&""].children"",""Clients"")"
The code will get the information from E4 and E5 from the worksheet.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
