• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Data Model Through VBA

Dick Dye

New Member
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?


New Member
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.