I am calling an Excel session from another application.
When run from the GUI (and Excel has focus), the Workbook
takes about 0.2 seconds to recalculate. When called from
my VB application however, recalculation takes around 25
seconds. When run from the Excel GUI and Excel does not
have focus, recalculation also takes 25 seconds.

I've seen other messages that describe the same problem
and associate the slow down with the difference between a
foreground and background Windows process. This does not
seem to be the complete problem because even if I
increase the priority of the Excel process, I cannot
significantly improve performance.

Any recommendations on how to improve recalculation
performance of background Excel sessions?

Hi Scott,

For the optimization of Excel, I'd suggest one MSDN article may provide some assistance for you on this issue. Please go to:
Recalculation in Microsoft Excel 2002
http://msdn.microsoft.com/library/de...c_xlrecalc.asp

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Wei-Dong,

Thanks for the reply. Unfortunately this does not answer
my question. I am not interested in speeding up
calculations, and instead want to prevent them from
slowing down when Excel is in the background. While Excel
is in the foreground, performance is acceptible. What I
need to know is how to make Excel think it is in the
foreground while being automated. When Excel is visible,
I can do this by clicking on the GUI. When Excel is not
visible, I don't know what to do.

regards,

- Scott W.


-----Original Message-----
Hi Scott,

For the optimization of Excel, I'd suggest one MSDN
article may provide some assistance for you on this
issue. Please go to:
Recalculation in Microsoft Excel 2002
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnexcl2k2/html/odc_xlrecalc.asp

Please feel free to let me know if you have any further
questions.

Does this answer your question? Thank you for using
Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and
confers no rights.


.

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 procedu
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...B;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/te...SOfficeDev.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.

From: "Scott Woyak"
Subject: Excel runs slowly in the background
Date: Sun, 30 Nov 2003 16:52:37 -0800
Newsgroups: microsoft.public.excel.programming

Wei-Dong,

Thanks for the reply. Unfortunately this does not answer
my question. I am not interested in speeding up
calculations, and instead want to prevent them from
slowing down when Excel is in the background. While Excel
is in the foreground, performance is acceptible. What I
need to know is how to make Excel think it is in the
foreground while being automated. When Excel is visible,
I can do this by clicking on the GUI. When Excel is not
visible, I don't know what to do.

regards,

- Scott W.


-----Original Message-----
Hi Scott,

For the optimization of Excel, I'd suggest one MSDN
article may provide some assistance for you on this
issue. Please go to:
Recalculation in Microsoft Excel 2002
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnexcl2k2/html/odc_xlrecalc.asp

Please feel free to let me know if you have any further
questions.

Does this answer your question? Thank you for using
Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and
confers no rights.

Greg,

Thanks for the help! This solution does work around my
problem. I'm using Excel 2002 and the slow down is a
factor of 100. I'm not entirely happy with forcing my
application to lose focus, but it is much better than
trying to explain why things are running so slowly.

Thanks again for the help,

- Scott W.

-----Original Message-----
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 procedu
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/te...SOfficeDev.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.

From: "Scott Woyak"
Subject: Excel runs slowly in the background
Date: Sun, 30 Nov 2003 16:52:37 -0800
Newsgroups: microsoft.public.excel.programming

Wei-Dong,

Thanks for the reply. Unfortunately this does not
answer
my question. I am not interested in speeding up
calculations, and instead want to prevent them from
slowing down when Excel is in the background. While
Excel
is in the foreground, performance is acceptible. What I
need to know is how to make Excel think it is in the
foreground while being automated. When Excel is
visible,
I can do this by clicking on the GUI. When Excel is not
visible, I don't know what to do.

regards,

- Scott W.


-----Original Message-----
Hi Scott,

For the optimization of Excel, I'd suggest one MSDN
article may provide some assistance for you on this
issue. Please go to:
Recalculation in Microsoft Excel 2002
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnexcl2k2/html/odc_xlrecalc.asp

Please feel free to let me know if you have any
further
questions.

Does this answer your question? Thank you for using
Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties,
and
confers no rights.





.