I have an application that makes use of excel Interop

Im using Microsoft Excel Object Library 10

The application does the following:
€¢ Loads an existing analysis spreadsheet
€¢ Sets Calculation mode to Manual (to prepare for efficient data import)
€¢ Clears the first worksheet (of previous input data)
€¢ Imports the text file into the first worksheet
€¢ Set Calculation mode back to automatic
€¢ Parses a field cells on a results Worksheet

I noticed while testing the application that the cells appear to
(re)calculate much faster when the objApp.Visible = true. (Improving
calculation speed from several minutes to ~ 25 seconds) This doesnt make
sense to me. My only thought is that excel is throwing up a prompt when my
spreadsheet is running invisibly and that this prompt eventually times out
allowing my method to eventually complete. I have already set DisplayAlerts
to false. The Analysis spreadsheet does not contain any macros so I wouldnt
have expected this to be a privilege issue.

I prefer never to display the spreadsheet.

If anyone has any suggestions I would greatly appreciate them

Thanks,

Jeff
€˜############################################
€˜code that opens the spreadsheet
objApp = New Excel.Application
objApp.DisplayAlerts = False
objApp.UserControl = False
objApp.Visible = False €˜True

objBooks = objApp.Workbooks

€˜objBooks.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMRU)

objBook = objBooks.Open(FilePath, 0, True, 5, String.Empty, String.Empty,
True, Excel.XlPlatform.xlWindows, ",", True, False, 0, True)
€˜##########################################

€˜public method that imports the text file
Public Sub importDataFromFile(ByVal FilePath As String)
Me.CalculationMode = Excel.XlCalculation.xlCalculationManual
'Import CSV file into data worksheet
importDataFromFile(FilePath, Sheets.Data)
Me.CalculationMode = Excel.XlCalculation.xlCalculationAutomatic
m_Calculating = False

End Sub

€˜##############################################

€˜ method that imports the text file into the WorkSheet
Protected Sub ImportDataFromFile(ByVal FilePath As String, ByVal
WorkSheetIndex As Integer)
Dim dataSheet As Excel._Worksheet =
CType(Me.objSheets(WorkSheetIndex), Excel._Worksheet) '
dataSheet.Activate()
ClearWorksheet(dataSheet) 'Must clear otherwise old data will just
be shifted to the right of new data


dataSheet.Range("A1").Select()
With dataSheet.QueryTables.Add(Connection:=String.Forma t("TEXT;{0}",
FilePath), _
Destination:=dataSheet.Range("A1"))
.Name = "Test_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = Excel.XlTextParsingType.xlDelimited
.TextFileTextQualifier =
Excel.XlTextQualifier.xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh(BackgroundQuery:=False)
End With

End Sub

Hello Jeff,

From your post, my understanding on this issue is: you wonder why the
ImportDataFromFile process is extremely slow only when
Excel.Application.Visible = false, and how to resolve it. If I'm off base,
please feel free to let me know.

First off, I suggest we identify which part of code in ImportDataFromFile
slows down the whole process when Application.Visible=false. Below are the
two possible approaches that can help us identify the location:

Approach 1. Debug the code lines. Step over each line of code in
ImportDataFromFile, and see which line hangs for a extremely long time. If
you are using Visual Studio, we can step over the code lines by pressing
F10.
Approach 2. Use a stop watch class to calculate each line's execution time:
http://www.codeproject.com/KB/vb/vbnetstopwatch.aspx. An
easier-to-implement stop watch is like:
Dim start As DateTime = DateTime.Now
'execute our code
Dim [end] As DateTime = DateTime.Now
Dim span As TimeSpan = [end] - start

I believe knowing which part of code slows down the process will help us
determine the underlying reason for the performance issue.

In addition, Jeff, I suggest you call Worksheet.EnableCalculation = False.
This will disable the recalculation of the sheet, and may accelerate the
import process.
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx
Does your target worksheet contain a lot of function(UDF) to be calculated?

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Jeff,

From your post, my understanding on this issue is: you wonder why the
ImportDataFromFile process is extremely slow only when
Excel.Application.Visible = false, and how to resolve it. If I'm off base,
please feel free to let me know.

First off, I suggest we identify which part of code in ImportDataFromFile
slows down the whole process when Application.Visible=false. Below are the
two possible approaches that can help us identify the location:

Approach 1. Debug the code lines. Step over each line of code in
ImportDataFromFile, and see which line hangs for a extremely long time. If
you are using Visual Studio, we can step over the code lines by pressing
F10.
Approach 2. Use a stop watch class to calculate each line's execution time:
http://www.codeproject.com/KB/vb/vbnetstopwatch.aspx. An
easier-to-implement stop watch is like:
Dim start As DateTime = DateTime.Now
'execute our code
Dim [end] As DateTime = DateTime.Now
Dim span As TimeSpan = [end] - start

I believe knowing which part of code slows down the process will help us
determine the underlying reason for the performance issue.

In addition, Jeff, I suggest you call Worksheet.EnableCalculation = False.
This will disable the recalculation of the sheet, and may accelerate the
import process.
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

Does your target worksheet contain a lot of function(UDF) to be calculated?

If Visible is set to true, is there any dialog popped up on your side when
the ImportDataFromFile is processed?

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Jeff,

Does the suggestion in my last reply help? Would you let me know the test
result based on debugging or clock watcher? Is there any dialog open when
excel is visible? If there is anything I can do for you, please let me
know.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Jialiang,

This was the first post from you on this thread. If you made a previous
post to this thread please submit it again.

The tests results were base on a release build of my application. I have a
visibility setting in my application configuration file that I edited between
tests. This visibility setting is used to set the visibility for the excel
instance.

No, there is not a dialog open when the excel instance is run as visible.

Please re-submit your previous suggests to the thread.

Thanks,

Jeff




"Jialiang Ge [MSFT]" wrote:

Hello Jeff,

Does the suggestion in my last reply help? Would you let me know the test
result based on debugging or clock watcher? Is there any dialog open when
excel is visible? If there is anything I can do for you, please let me
know.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Hello Jeff,

There seems something wrong with newsgroup post synchronization in this
thread. I am sorry for it. Below is my initial response posted on March 25.


Hello Jeff,

From your post, my understanding on this issue is: you wonder why the
ImportDataFromFile process is extremely slow only when
Excel.Application.Visible = false, and how to resolve it. If I'm off base,
please feel free to let me know.

First off, I suggest we identify which part of code in ImportDataFromFile
slows down the whole process when Application.Visible=false. Below are the
two possible approaches that can help us identify the location:

Approach 1. Debug the code lines. Step over each line of code in
ImportDataFromFile, and see which line hangs for a extremely long time. If
you are using Visual Studio, we can step over the code lines by pressing
F10.
Approach 2. Use a stop watch class to calculate each line's execution time:
http://www.codeproject.com/KB/vb/vbnetstopwatch.aspx. An
easier-to-implement stop watch is like:
Dim start As DateTime = DateTime.Now
'execute our code
Dim [end] As DateTime = DateTime.Now
Dim span As TimeSpan = [end] - start

I think knowing which part of code slows down the process may help us
determine the underlying reason for the performance issue.

In addition, Jeff, I suggest you call Worksheet.EnableCalculation = False.
This will disable the recalculation of the sheet, and may accelerate the
import process.
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

Does your target worksheet contain a lot of function(UDF) to be calculated?

If Visible is set to true, is there any dialog popped up on your side when
the ImportDataFromFile is processed?



Again, I am sorry for the inconveniences caused by the newsgroup system. I
have reported the system problem to the system owner through internal
channels. They will look into it and fix the problem.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Jialiang thanks for your suggestions. Unfortunately, I still havent solved
the problem.

Setting the calculation mode back to automatic is the CPU hog. I set
dataSheet.EnableCalculation = False but this didn't appear to have an impact
on speed. The import of the text file to the data page is relatively quick (
less than a second)


Public Sub importDataFromFile(ByVal FilePath As String)
Me.CalculationMode = Excel.XlCalculation.xlCalculationManual
'Import CSV file into data worksheet
importDataFromFile(FilePath, Sheets.Data)


Me.CalculationMode = Excel.XlCalculation.xlCalculationAutomatic
'(objApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic)

m_Calculating = False

End Sub



The chart below show execution times for setting the spreadsheet back to
automatic calculation after the csv data import.

Run Column: Shows the consecutive runs. Here I'm reusing the same excel
instance, but loading new csv files. Note this execution time get
significantly faster each run even in manually importing the csv files into
the spread sheet.

Manual: This shows the same data import (edit text import) and calculation
done in excel without interop

Visible: Execution time when running with Excel instance visible

NotVisible: Execution time when running with Excel not visible
-------------Execution time in Seconds--------------
Run # Manual Visible NotVisible
1 50 59.7 301.4
2 9 47.3 67.8
3 ~0.5 0.37 0.3
4 ~0.5 0.28 0.3
----------------------------------------------------

I also noticed that when running with the excel instance visible but
minimized yielded the same
results as running with the instance set to not visible.

I tried calculating the pages individually but didn't see a real performance
improvement. Nearly all of the time is spent calculating one of the analysis
Worksheets. I noticed this worksheet uses Dmax, Dmin, and Daverage. Could
the searching in these functions cause strange execution times? (it gets
faster each time when using the spreadsheet manually )

Is there anyway clever way to get this waiting out of the way in advance?

My users will rarely be running more than one analysis a day. Therefore
they will always experience
a long wait. I really don't want to have to run with excel visible. I
think I should be able to get the cell calculation to execute invisibly at
least as fast as when the instance is executing visible.

Thanks,

Jeff



"Jialiang Ge [MSFT]" wrote:

Hello Jeff,

There seems something wrong with newsgroup post synchronization in this
thread. I am sorry for it. Below is my initial response posted on March 25.


Hello Jeff,

From your post, my understanding on this issue is: you wonder why the
ImportDataFromFile process is extremely slow only when
Excel.Application.Visible = false, and how to resolve it. If I'm off base,
please feel free to let me know.

First off, I suggest we identify which part of code in ImportDataFromFile
slows down the whole process when Application.Visible=false. Below are the
two possible approaches that can help us identify the location:

Approach 1. Debug the code lines. Step over each line of code in
ImportDataFromFile, and see which line hangs for a extremely long time. If
you are using Visual Studio, we can step over the code lines by pressing
F10.
Approach 2. Use a stop watch class to calculate each line's execution time:
http://www.codeproject.com/KB/vb/vbnetstopwatch.aspx. An
easier-to-implement stop watch is like:
Dim start As DateTime = DateTime.Now
'execute our code
Dim [end] As DateTime = DateTime.Now
Dim span As TimeSpan = [end] - start

I think knowing which part of code slows down the process may help us
determine the underlying reason for the performance issue.

In addition, Jeff, I suggest you call Worksheet.EnableCalculation = False.
This will disable the recalculation of the sheet, and may accelerate the
import process.
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

Does your target worksheet contain a lot of function(UDF) to be calculated?

If Visible is set to true, is there any dialog popped up on your side when
the ImportDataFromFile is processed?



Again, I am sorry for the inconveniences caused by the newsgroup system. I
have reported the system problem to the system owner through internal
channels. They will look into it and fix the problem.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================