Hello Scott,
I am familiar with the issue you describe. The problem is related to how
Excel reacts when it loses the focus. Internally, Excel will sometimes call
Sleep, or otherwise throttle itself down when it loses the focus. Which
version of Excel do you have? There apparently was some work done in Excel
2002 (and newer) to help on the calculation performance when Excel does not
have focus. If possible, I recommend you try Excel 2002 or newer to see if
there is an improvement in performance over older versions of Excel.
If you are still seeing this problem with Excel 2002 or newer, my advice is
to write code in your VB6 application to give Excel the focus. You can do
this with the SetForegroundWindow API function, even if Excel is not
visible. Here is a VB6 code sample that shows how:
Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetForegroundWindow Lib "user32" _
(ByVal hwnd As Long) As Long
Private Sub Command1_Click()
Dim oExcel As Object 'Excel.Application
Dim oBook As Object 'Excel.Workbook
Dim hwnd As Long
' Enable an error handler for this procedure:
On Error GoTo ErrorHandler
' Start a new instance of Excel for automation:
Set oExcel = CreateObject("Excel.Application")
' Get hwnd of Excel window using approach
' from KB 258511. Note, if using Excel XP
' you can use oExcel.Hwnd instead:
oExcel.Caption = "besuretofindthisinstance"
hwnd = FindWindow("XLMAIN", oExcel.Caption)
oExcel.Caption = Empty 'reset to default Excel caption
' Now set focus to Excel before carrying out
' actions that need Excel to recalculate:
SetForegroundWindow hwnd
' Open the workbook:
Set oBook = oExcel.Workbooks.open("c:\test.xls")
' Perform calculations, etc.
' Save the workbook:
oBook.Save
Cleanup:
' Quit Excel instance:
On Error Resume Next
oBook.Close SaveChanges:=False
Set oBook = Nothing
oExcel.Quit
Set oExcel = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description, vbMsgBoxSetForeground
Resume Cleanup
End Sub
Try your code with and then without the SetForegroundWindow call to test
for a difference in performance. If there are times you want focus to shift
back to your VB6 form, you can call the Setfocus method of your form object
(Me.Setfocus if the code is within that form object).
For information about my use of SetForegroundWindow for Excel, the
following article was used as a reference:
258511 - HOWTO: Obtain the Window Handle for an Office Automation Server
http://support.microsoft.com/default.aspx?scid=KB;en-us;258511
For information and sample code for integrating Office with Visual Basic,
Visual C++, Internet Scripts, and other programming languages, please see
http://msdn.microsoft.com/library/techart/VSOfficeDev.htm. This site
contains the most up-to-date information for using developer tools for
Office integration and extensibility.
Best regards,
Greg Ellison
Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? Please visit the Microsoft Security & Privacy Center
(
http://www.microsoft.com/security) for the latest news on security updates.