PowerPivot Manipulation Through VBA? A few general questions

[mrmmickle1]

I have a large amount of detail files produced quarterly that have 12 worksheets in them. Sometimes these files can exceed 45 MB. These files are very "heavy" and difficult to send back and forth through email. I noticed that by adding the raw data into PowerPivot I can compress the file size by 85%. I would love to utilize PowerPivot in order to manage the file size. My issue is that I currently create Pivot Tables on the fly (Through VBA code when a button is pressed to look at different cuts of data) based on a mapping table. The reason these are created on the fly is because I have about 30 different options... so I do not want to house these all in the file due to space/file size issues....with this business case it brings a few questions regarding my issues and PowerPivot


Questions:

I'm looking to load data into a PowerPivot model through VBA is this possible? (I create 63 Reports through VBA quarterly) Could I programmatically load the detail into a PowerPivot Model?

Can I create PowerPivot tables on the fly using VBA?

If I create PowerPivot tables in a file and send the file to someone without PowerPivot installed will the file still work properly?

If I created a fit all template with all 30 or so Pivot Table views. Can I programmatically delete the ones that are not relevant for the each of the 63 reports?


Does anyone have any suggestions?

 

[Matt Allington]

Questions:

I'm looking to load data into a PowerPivot model through VBA is this possible? (I create 63 Reports through VBA quarterly) Could I programmatically load the detail into a PowerPivot Model?

I'm sure you could, but I wouldn't recommend it. You should investigate Power Query as a tool. PQ is designed for doing this type of work. You will love it.

Can I create PowerPivot tables on the fly using VBA?

Excel 2013 has a limited VBA object model for power pivot. It is much better in 2016. I suggest you load the data with power query to power pivot and then use DAX queries to materialise the data you want into the spreadsheet. Read about DAX studio and some tips on how to do that here Getting Started with DAX Studio -

If I create PowerPivot tables in a file and send the file to someone without PowerPivot installed will the file still work properly?

No

If I created a fit all template with all 30 or so Pivot Table views. Can I programmatically delete the ones that are not relevant for the each of the 63 reports?

It depends why you need pivot tables. If all you are trying to do is extract he data, then consider using DAX queries as covered in the DAX studio post. Yes you can programmatically delete pivots if you go down that path.

You might want to read my power query series of blogs that covers ways to combine data from multiple workbooks. http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

good luck.

 

[mrmmickle1]

Matt,

Thanks so much for your input. I appreciate your insight. I'll look into these options.