We are having an issue with our servers taking large amounts of time (from 1 minute to 1 HOUR) to open an Excel template via Automation. We have narrowed the issue down to the use of combo boxes within the template. If we remove all 38 combo boxes, the template opens in less than 1 second. As we add combo boxes (VB objects not Form objects) back to the template, the load time grows again.
We have also tried a test where we use a blank workbook and create the combo boxes on the fly via code (OLEObjects.Add) but it takes the same amount of time (1 minute to 1 hour) to do so.
We have 6 servers, 5 of which are Win 2K. The 6th server is Win 2K3. All have Office 2000 with SP3 installed. The 6th server is much faster than the others at loading the template but still takes a minute.
Our developer machines (Windows XP) all open the template instantaneously via Automation:
NOTE: We can open the template directly on the server and it is instantaneous. However, via Automation, it takes the delayed amount of time.
Set Xl = CreateObject("Excel.Application")
Set XlApp = Xl.Application
We have confirmed all machines (including developer's machines) have the same version of FM20.DLL on them.
Does anyone have any ideas what could be causing this and how we can resolve the issue? Clearly, we cannot afford for the server to be choking along for this duration of time as the opens the template during our reporting process (Visual Basic 6.0 app, using Excel for output target)