Can I write from VBA directly to the Power Pivot Data Model?

[gazpage]

And if not, what is the closest work around.

I have a VBA macro that outputs c.10m rows. I can't output to and Excel table due to the row limit. Ideally I'd like to turn a VBA array directly into a PowerPivot table (or even better overwrite an existing one so that relationships etc would be maintained.)

Failing that I'm thinking of writing to csv and then importing, but it seems like a wasted step.

 

[Matt Allington]

I would be very surprised if you can. There is an object model in Excel 2016 but I doubt that includes the ability to to write records given the way power pivot loads data in columns. I suggest you write to csv first, or change from a vba solution to Power Query if possible.

 

[gazpage]

Thanks. That's what I feared. VBA is necessary to at least cycle through by 100 x 5000 scenarios.

 

[macfuller]

If your VBA is running against an Excel or .csv source and the data transformation isn't mathematically complex then it might be possible to replicate your VBA actions in PowerQuery. Without knowing the data or code it's hard to know if you'd be saving time with PowerQuery (assuming the transformations are possible) but you could use Power Update or similar tools to run it as a batch job overnight.

 

[gazpage]

Thanks for the suggestion, but the 'transformations' are a complex project finance model with cashflow waterfalls, forward looking debt drawdowns etc. Not something that can realistically be done in DAX or M. I think I will write out to csv as suggested by Matt.