Excel 2003 VBA Server Processing and Remote Desktop Connection

Question

Hi Guys and Girls,

apologies if this isn't considered right for SO but we have a strange problem with processing Excel Macros on our servers.

We have over 100 excel spreadsheets which are created overnight on our server by WebFOCUS which are all formatted by macros. The spreadsheet is created using a template, the macro runs and then deletes the code and saves the spreadsheet.

The problem we have is that if we use a Remote Desktop connection into the server this process runs in a 10th of the time. What we can't work out is why having a remote connection open (which in theory will be using more CPU/Memory) makes the process run so much faster.

the number of spreadsheets we need will only increase over time and has in fact already reached a number where they are not completed in time for the start of the working day.

Obviously there is a limit of only two RD connections at a time so there is no guarantee we can actually get a connection to the server to speed the process up. We can use the console connection but its the speed issue that concerns us

does anyone have any ideas? even if just to say its not a VBA/Macro problem that will give us a place to start, otherwise we are baffled.

Answer 1

I've noticed that VBA performance can be severely negatively affected when Excel does not have focus on the desktop, i.e. when Excel is not the active application within Windows the VBA runs much slower. I can only imagine that this must be due to changing priority for the excel.exe process between the two scenarios. It could well be that similar priority switching is taking place when you have your Remote Desktop connection active and the Excel window active. If this is the case, then one solution may be to ensure that the priority of the scheduled process is set to 'High' or even 'Realtime' (see Task Manager -> Right click a process -> Set Priority -> ). Perhaps your scheduling application has the capability to do this or if you just have a constantly running Excel process then perhaps you can manually set it after starting it intially.

Answer 2

May give a try to the following:

At beginning of the macro Put

Application.ScreenUpdating = False

and at end of the macro put

Application.ScreenUpdating = True

and also check for more tips at

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Answer 3

Using a 3rd party Excel compatible spreadsheet component such as SpreadsheetGear for .NET or one of our competitors will be much faster and save you a lot of trouble.

Microsoft explicitly recommends against automating Excel on a server (see this article).

Disclaimer: I own SpreadsheetGear LLC

Answer 4

I agree with AdamRalph that Excel does not have focus and this is adversely affecting the VBA execution. You should consider the SpreadsheetGear product for this type of automation, as is developed for this type of process.