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