Using Data Model Through VBA

[Dick Dye]

Is there a way to take advantage of the PowerPivot Data Model in VBA? An example would help:
I get a weekly hours report that lists the hours each employee expended the previous week. The report is in the form "Employee ABC123 spent 35 hours on your task." Obviously, the data is in columns but the concept in the sentence applies.

I have a separate table that tells me that ABC123's name is John Doe.

I have linked the two tables through the Data Model so that PowerPivot correctly connects ABC123 to John Doe and gives me an hours report by name rather than employee number.

In VBA, I can do a search and learn that ABC123's name is John Doe.

I would prefer to use the Data Model in VBA and avoid the search. I suspect it would be faster and I'm sure it would make the code simpler and more readable. I would like to directly access the employee's name from their employee number via VBA. Is there a way to do that?

 

[Chihiro]

I'd imagine you'd be using query. Not sure if this would prove to be faster though.

Below link shows how to write DAX Summary Query via VBA
http://blog.learningtree.com/writin...sing-powerpivot-and-excel-querytable-objects/

And another,
http://social.technet.microsoft.com...es/19941.query-power-pivot-dmvs-with-vba.aspx

 

[jonesjennings]

Good Evening,

From what I gather, this enquiry is right on the leading edge of what can and what can't be done. I've been trying to automate my data model for most of this week, with some success so far.

The general approach I've been taking is to create pivot tables manually, then to use VBA to manipulate those pivot tables to get what I need.

I've been using something like:

Myworkbook.Pivottables("PivotTable1").Filters("[Employees].[Surname].[Surname]").CurrentPage = "[Employees].[Surname].&[Doe]"

(Syntax is probably wrong here, as I'm not on the work laptop. I'd recomend playing with Macro-Recorder for this, as it's a bit tricky to relate case-studies to your own data).

Hope this helps.