I have a report running on our EM server
I have a macro that was working for for a few months. The last thing my macro does is build a pivot table. I use EM excel command to call the macro. The issue I have seen lately is that randomly the pivot table will be created as expected and other times the macro runs everything else, but does not do the pivot. Any thoughts on what may be causing this? I do know other reports that also run macros some times may be running at the same time. I did run this on my EM desktop and it worked fine. Any advice?
Without seeing the Excel file, code, setup, etc. My "shot in the dark" would be a timing issue(?) - if it runs "sometimes". Before and after the chunk of code creating the pivot, insert the statement "DoEvents". This releases "control" for other (background) processes to run then returns to the macro (to continue). I've had to insert this on numerous occasions to get code to run consistently. It does slow the macro down just a touch (as it's basically "waiting" to get control back).
If it does work, try commenting out one, then the other, of the "DoEvents" statements and check for consistent runs. If it runs with only one of them in place, keep that and ditch the other (to get a little more speed back in the process vs. both statements running).
If the Excel file is something you're able to share, feel free to send it along. If it contains sensitive data and/or requires an NDA to view it, you can send it directly to my email CraigT@CDTCSLLC.com .