Hi all,
I am starting a new project at our firm and I need some basic guidance on how to achieve what I'm going for. I can probably work out the details or come back with specific questions, but right now I want a general blueprint or approach.
Basically, we have a new (and better) time system, so a macro I made earlier that takes excel generated time reports and turns them into a clean pivot table for presentation- that I spent a good amount of time on- is now kind of useless, but the process was very educational and I can do even better work. In a way I should be able to do what I did before and it should be even easier as the reports do not have breaks in the rows of data but is more properly formed as as table.
There are two things that throw me off right now and take me out of my element:
First, I was wondering if Power Query could be used. For some reason, since I learned about PQ, I want to find a place to use it and this seems like it could be a good place, but maybe it isn't. We will need to export a table of data from our system to excel, strip away some of the columns, and we will need to make a pivot table of that with the staff names as the columns, the service descriptions as the rows, and the sum of the hours per service description as the values. What would indicate the use of Power Query as opposed to VBA if we would have to use this on multiple newly generated workbooks? I suspect that last fact may invalidate it's use. I think Power Query M language is like the xlsm workbooks. They aren't part of your Excel system, right? They are bundled only with individual workbooks otherwise they're impracticable.
Second the new reports only have the time codes (ints), not the descriptions (strings) of what those codes represent, which we need to show up on the table, so I'd have to translate them, maybe with a lookup table or something.
I extracted a pdf mapping of all of this into a csv file so maybe that can be used for lookup in a macro or something. If strictly VBA and not PQ, would this data be stored as an array in my code or would it be stored as a separate file in the C Drive that is read from VBA? That would require more learning, but that's what I love.
For example, the Svc Code "15000" represents Description "ADM Administration" and there are other columns that go with it as well but which aren't as important for these purposes.
The Svc Code A2300 represents "AUD Acounts Receivable" - i.e. audit procedures performed on testing accounts receivable. Firm wide we have a few hundred codes, for tax, audit, marketing, admin, business services, etc.
Can M from Power Query be embedded in VBA? Would that be a good approach?
Otherwise I can probably make it work.
Thanks for general guidance to an abstract question.
Bookmarks