VBA to Read Entire PowerPivot Column

swingr45

New Member
Joined
Jul 28, 2014
Messages
13
I'm working on creating financial reports (Income Statement, Balance Sheet, etc.) through PowerPivot. I've got all the data set up the way I need it and ran a small test sample to verify it would work. It did, but I was using the JetReports add-in to refresh my G/L Accounts to make sure that none were missed. Essentially, it would read every Account No. from my table and create a row for that No., copying the other columns on that row to each subsequent row. This way, I could write one formula that would read that Account No and find the corresponding data from PowerPivot.

My problem is that I can't use Jet Reports with the 64-bit Excel and I have too much data for 32-bit to handle. I figured I could write a macro that would replicate the Jet Reports functionality by reading each value in my Account No table in PowerPivot and creating a row... However, I can't figure out how to access the data from VBA. Can anybody help me out, please?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I might have more questions than answers...

2010 or 2013 version of Excel?
You were using Jet Report for what now?
You are saying it DOES read Power Pivot data directly out of the model?
How many rows are we talkin' ?

That I know... vba can't read data out of the model, but if I understood your scenario better... maybe we could work something with cube formulas or dax table queries (if 2013)... or something. But I'm unclear what problem JetReports was solving for you.
 
Upvote 0
Thanks for trying to help me, Scott. Sorry I wasn't clearer initially. I was actually able to get my PowerPivot connections set up after a system restart. I hadn't gotten too far into the setup when I received the error and after reflecting on it, I thought that it seemed way too early to have maxed out it's capabilities. I may not need this solution anymore at the moment, but the knowledge would likely be valuable in the future.

I was trying to use 2010, but also have access to 2013.
JetReports is a 3rd party addin that makes reporting from a Microsoft Dynamics system much easier. It has become such a daily staple in my work life that I tend to assume more people are familiar with it than actually are. For this report, it was accessing the SQL database from my Dynamics NAV instance and reading the G/L Account table. It would then insert a row for each value of the field "No." that existed in the table. With each row, it would copy all other columns and their formulas/values. So I would only need to write 1 row with the all the calculations I needed and it would produce a line for each Account using those same calculations. (Hope that was clear enough)
I have not yet created a macro, so I don't have VBA reading directly out of the model. I have used cube formulas to return the calculations that I need.
The number of rows that I need to automatically produce on the report will only be around 200 or so, but the total number of rows in the model is a couple million.

It sounds like what I was hoping for won't be possible if VBA can't directly access the model (it'd be great if I was wrong.) But I want VBA to loop through a single column from the model and put each value in cell A2:A200, for example. At the same time replicating the formulas in cells B:G into each new row.

I have been able to work through my initial problem, so this solution is no longer urgent to me, so please don't dwell on it if I have made it too difficult. Thanks again!
 
Upvote 0
Sorry for the slow reply, I'm traveling this week :)

Pretty sure no "pretty" vba solution for you, though... if you were hardcore you might be able to grab the connection string and send queries directly to the cube... might require mdx, but maybe dax would work? This is certainly not something I (or probably anybody sane) has played with.

However... if you are already familiar with cube formulas would that not work for you? (via CUBESET & CUBERANKEDMEMBER)
 
Upvote 0
Absolutely no need to apologize. You've already been a huge help to me getting started with Powerpivot and saved me a ton of wasted hours.

As I read more into this, I started to get the feeling that there would be no "pretty" solution. I'll keep the cube formula possibilities in mind, but at the moment the extent of my cube formula knowledge is using the automatic "Convert to Formulas" tool and then reproducing/manipulating that base formula. I am still very much a newb to all of this, but trying to learn, as I have already seen how powerful it can be and how much time it can save.

But for now, this VBA project will be set on the backburner. Thanks for giving your thoughts on it, they are always greatly appreciated!
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top