Microsoft Remote Desktop Services (RDS) on Server 2012 R2
Microsoft Dynamics GP 2013 with Management Reporter 2012 (hosted)
Office 2013 Professional (32 or 64 bit)
We recently experienced an issue with certain Excel files opening very slowly. When attempting to open a moderately sized Excel document, less than 1 MB file, it was taking 10-20 minutes to load (if it loaded at all!). The documents in question were financial reports with about 215 worksheets. Each worksheet had some numbers and text but no complicated formulas, macros or visual basic. These are simple documents, usually around 750kb in size. So what was the problem?!?!?
The documents typically giving the issue were created from data in Dynamics GP using Management Reporter 2012 (MR). When we migrated from Dynamics GP 10 to version 2013 we were really excited to use all the new features offered in the newer version. One of the most attractive features is the integrated reporting tool Management Reporter 2012, which is Microsoft’s replacement for FRx. One big difference from FRx is that when you generate a report and export it to Excel, the default is to save every worksheet in “Page Layout” view. According our GP hosting partner WebSan, this setting cannot be changed in MR.
The result is excruciatingly slow performance when opening files with multiple worksheets in an environment that uses printer redirection. Aha! The file loads slowly because Excel is checking the printer to verify if each page layout will work. If the file is loaded on a local desktop it will open completely in less than 20 seconds. But the repeated requests to the printer through the redirection and network for every worksheet send your app into an endless loop of check and recheck. There are 2 potential quick and dirty solutions that will ease your pain.
The user experience:
- User attempts to open an Excel file (our has about 215 worksheets all containing simple numerical data – no formulas, macros or VB)
- Excel application becomes whited out and the title bar says “Not Responding”
- Application hangs but if left untouched the file usually opens in 10-20 minutes, during this time other operations are limited and Excel is locked in processing mode
- If the user is impatient, they may attempt to close the app altogether, but they will only be offered choices that will keep them in a continuous cycle of samsara
Microsoft Excel is not responding, AppHangB1 (could be a thousand causes)
a. If the user selects “Check for a solution and restart the program” – the application will attempt to restart and the user will be rewarded with the same thing over and over for eternity
b. “Restart the program” tries to recover the endless loop
c. “Wait for the program to respond” is not very attractive but it’s the only one that will actually work
- The user will have to wait a really long time for the file to open, 10-20 minutes or more. Go get a coffee, take a walk outside, chat with people by the water cooler, maybe even get lunch because it takes a while. Then the document finally opens! Usually.
- Opening the document in safe mode will not work, 32 bit or 64 bit Office don’t matter (I tested it). But opening the file on a local machine will work.
- Open the Excel file on a local desktop (not in RDS) and select all worksheets by right clicking on the title of any worksheet tab and choosing “Select All Sheets”. It may take a few seconds to load.
- On the lower right hand of the screen select “Normal” view
- Once all sheets are changed to the Normal view, right click any sheet title and select “Ungroup Sheets”
- Save the document and attempt to reopen it in RDS and it should pop open in 1-2 seconds.
Use this solution if the file must be opened in RDS for the first time and the local machine is not available.
- Set the default printer to “Microsoft XPS Document Writer”
- Open the Excel file and it should open quite quickly, in our environment it takes about 20 seconds. You may have to train users on setting their default printer if you have not locked this setting in RDS. This solution will obviously also speed things up if used on the local desktop.