Excel Calculation is faster when visible than when Visible=False?


I have an application that makes use of excel Interop

I’m using Microsoft Excel Object Library 10

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

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 doesn’t make
sense to me. My only thought is that excel is throwing up a prompt when my
spread*** 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 spread*** does not contain any macros so I wouldn’t
have expected this to be a privilege issue.

I prefer never to display the spread***.

If anyone has any suggestions I would greatly appreciate them

Thanks,

Jeff
‘############################################
‘code that opens the spread***
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 work***
importDataFromFile(FilePath, Sheets.Data)
Me.CalculationMode = Excel.XlCalculation.xlCalculationAutomatic
m_Calculating = False

End Sub

‘##############################################

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


data***.Range("A1").Select()
With data***.QueryTables.Add(Connection:=String.Format("TEXT;{0}",
FilePath), _
Destination:=data***.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


.