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.
=================================================