Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=False?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=False?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=False?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=False?
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. ================================================= |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=Fal
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. ================================================= |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=Fal
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. ================================================= |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=Fal
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. ================================================= |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=Fal
Hello Jialiang,
There are a few differences that are worth noting. ’ The data being imported is always CSV In all of my tests I have been importing data from .CSV files. (there wasnt a delta because of the type of file.) ’ When running excel manually (without my automation code) it takes ~50 seconds The spreadsheet recalculates faster after successive data imports. This is true even without my automation code. When I run it like an excel user it takes 50 seconds to recalculate the cells the first time, then 10 seconds, then it is nearly instantaneous. Im wondering if it some kind of ambiguity in the order of operations in the spreadsheet? ’ When Excel. Application.Visible = True (using automation code) it takes ~60 seconds. With successive data imports into the same excel instance the recalculation gets faster. By the 3rd data import recalculation is nearly instantaneous. ’ When Excel.Application.Visible = False or excel is visible but minimized the recalculation after the first data import takes about 5 minutes. On the second import it takes about ~70 seconds and on the third import it is nearly instantaneous. Im wondering if there is some kind of race condition or order of calculation issue that is impacted by the visibility of the excel instance. I mentioned the long processing delays with the author of the spreadsheet and a new very stripped down version was created. This version appears to execute with the Excel.Application.Visible = False in a fraction of a second on the first attempt. I plan to start out with the original spreadsheet and strip it down with the author until I see what is consuming all the CPU time. Thanks, Jeff "Jialiang Ge [MSFT]" wrote: Hello Jeff, Thank you for the test result. Let me summarize the present information in case I misunderstand something: ------------------------------------------------------ Problem Description: We are automating Excel with Excel Object Library 10 to import source data (csv or txt file) into a spreadsheet (xls) by calling dataSheet.QueryTables.Add. The target sheet contains a lot of functions (e.g. DMAX, DMIN, DAVERAGE) to be calculated. In order to improve the performance, we turn off auto-calculation before the process (Excel.XlCalculation.xlCalculationManual), and turn it on when the job is finished. (Excel.XlCalculation.xlCalculationAutomatic) A. When we import data from csv to xls: A.1 If Excel.Applicaiton.Visible = True, the whole process takes 50 seconds A.2 If (Excel.Application.Visible = False) or if (Visible = true but Excel is minimized), the whole process takes 5mins. And most of the time is spent on turning on auto-calculation, which means it takes a very long time for Excel to re-calculate the functions in the spreadsheet. B. When we import data from txt to xls: No matter if Application.Visible is true or false, the whole process can be finished within 1 second. Objective: To improve the performance of the condition A.2. We expect the job can be finished within 50seconds with Application.Visible = false. Solutions we have tried: 1. Set Worksheet.EnableCalculation = False, but it does not help 2. Confirm that no dialog is opened when Application.Visible = true -------------------------------------------------------- Is my summary correct? Please supplement if I missed something important. I build a test project based on the summary above and your code snippet, trying to reproduce the issue on my side. However, my program takes only one second around to finish the import job. You can download the attached test project (including the csv and xls files I used for test) with Outlook Express or Windows Mail, compare it with yours, and see if I have any misunderstanding. I have also queried the issue in Microsoft's internal database, but I failed to find any similar issue report. I am currently discussing the issue with some Office product developers, trying to reproduce the issue, and to find out the underlying reasons. I am also looking forward to your confirmation of my above summary. By the way, if you feel this issue is very critical and urgent to your business, it is recommended that you contact Microsoft Customer Support Services (CSS) via telephone so that a dedicated Support Professional can assist you in a more efficient manner. You can contact Microsoft Product Support directly to discuss additional support options you may have available, by contacting us at 1-(800)936-5800 or by choosing one of the options listed at http://support.microsoft.com/common/...fh;en-us;cntac tms. 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. ================================================= |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=Fal
Thanks, Jeff, for the confirmation. Based on my discussion with the
developers, it's very likely that the low performance is due to some function use in the spreadsheet. We have not figured out the exact reason without a reproduce. Is it possible for you to send the spreadsheet to me if your company's policy allows? You may remove the critical/sensitive data from the spreadsheet, and send it to my mailbox Thanks, 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. ================================================= |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation is faster when visible than when Visible=Fal
Hello Jeff,
The Office developers and I have researched the issue for several days, but, I am sorry, we still cannot reproduce the issue and figure out what's going wrong. A reproducible sample workbook from you will be very helpful to us. My mailbox address can be found in my signature. In addition, looking at the nature of this issue, it would require intensive troubleshooting which would be done effectively with direct assistance from a Microsoft Support Professional through Microsoft Product Support Services. You can contact Microsoft Product Support directly to discuss additional support options you may have available, by contacting us at 1-(800)936-5800 or by choosing one of the options listed at http://support.microsoft.com/common/...fh;en-us;cntac tms. 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. ================================================= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make a few rows visible and hide some others ... faster | Excel Programming | |||
excel 2007 problem, activewindow.visible = false does not work | Excel Programming | |||
Application.Visible = False | Excel Discussion (Misc queries) | |||
Worksheet has to set to visible as it is not visible after saving and closing Excel by VB. | Excel Programming | |||
Visible=false | Excel Programming |