Turn-Around-Time Excel macro
Once again we have reports that the system has chucked out that need a lot of work. In this example we are reporting the turn-around-times for a monthly report. In this example, the Cerner Powervision app gives us a report on each tests’ TAT. This report is exported to Excel and the user counts how many tests exceeded TAT, and calculates the percent meeting target TAT. TEDIOUS! Excel macros to the rescue:
Instead of making this into a full-blown tutorial I will just list the sticking points and post the macro. If you have any questions just add a comment.
What to do when you can’t use Personal.xlsb to store your macros: (only for people using Personal.xlsb otherwise skip this)
The Powervision app exports the reports to Excel, but it’s not loading my Personal macros file. This is because it’s running a Citrix Excel (I’m guessing) instead of using my pc’s excel. For this you just have to put your macro code into a workbook, and then load that workbook with the Citrix excel along with the exported report. Your macro code can still do everything it needs to, having Personal macros is just a convenience. I have named my workbook “ProcessTATexportsMacroXl2003.xls”. It doesn’t actually have or import any data, it just contains my macro code.
The app exports each report to a separate workbook, this leads to another problem: