As of late I am experiencing an incredible slow down with Excel 2003
and formatting. I have code in Visual Basic 6 and I am open a
template Excel file. The excel file is filled with records from a SQL
Server data that is often formatted based on certain criteria. None
of the formatting can be off loaded to the template file,
unfortunately. Has anyone else experience this with Excel or the OLE
Excel.Application object from a VB6 application. Any suggestions
would be greatly appreciated for I seem to be all out of options.
Again, I know it's not the database call or anything weird like that
it is definitely the formatting code acting on the Excel.Application
object.

Thanks all

-Thanas

What is exactly the problem(s) your are facing? Slowness?

It seems you are doing Excel automation with your VB6 exe. How the app get
data into the Excel sheet? Do you update the sheet cell by cell? How much
data is involved.

If you update the Excel sheet by automating Excel from external app, with
just moderate amount of data, it would be very slow. That is the nature of
automation: data must be transferred between two processes.

Not knowing what exactly your app does with Excel sheet, I could not say
anything more.


wrote in message
oups.com...
As of late I am experiencing an incredible slow down with Excel 2003
and formatting. I have code in Visual Basic 6 and I am open a
template Excel file. The excel file is filled with records from a SQL
Server data that is often formatted based on certain criteria. None
of the formatting can be off loaded to the template file,
unfortunately. Has anyone else experience this with Excel or the OLE
Excel.Application object from a VB6 application. Any suggestions
would be greatly appreciated for I seem to be all out of options.
Again, I know it's not the database call or anything weird like that
it is definitely the formatting code acting on the Excel.Application
object.

Thanks all

-Thanas

Similar to Norman's response, I also couldn't answer as there's too many
variables to say, such as do you have auto calculation in place. How much
of an area are you formatting? If you using Conditional Formatting, are you
using formulas within it, which causes calculation to take place when in
auto mode. For this reason, you may want to try to have it in manual mode,
if there's a lot of calculations in the file(s).

How are you updating the data? Are you using MS Query or some other means
such as via a third party tool? As for MS Query, I hate it cause it's such
a memory leaker that of the 12 production files that gets it's data updated
to the summary files daily, if I was to use MS Query, it would cause Excel
to crash during the time it's updating the 3rd file, but yet, with the way I
use VBA to do the updating instead, I can run Excel for a couple of weeks
before I would need to reboot the system, and that's also with me doing a
lot of other things on the system too.

MS Query depends on ADO, which ADO within Excel has been documented to be a
memory leaker. I therefore mark MS Query as unavailable during installation
process due to it's more severe ill side effect.

I also update data from a database into Excel, but I never experienced the
slow down that you are claiming. Even in my production files that has
10,000's of range names (note, can't exceed 65536 defined names within a
single workbook else Excel puts the file into repair mode when it's opened,
which then the file is useless after that as a lot of things are lost. Yeah
yeah, spec help file states it's limited to system resources, but it's not
true as I have exceeded the capacity of the 2 byte Count property on the
Names collection object in the past to run into this repair mode issue. But
even then, the file becomes unstable after passing up the 32,768 defined
names give or take a couple, such as pulling up the workbook properties has
been known to go into an infinite loop cycle, and eventually causing Excel
to crash as Excel eats up more and more and more system resources.)

We use ShowCase Strategy Query provided by SPSS to pull data into Excel from
our main DB and outside of some issues, though unrelated to any sort of
slowness issues to Excel, I haven't had too much of a problem updating
Excel. Of course, my knowledge of the query program far exceeds the typical
program user, so I have ran into and uncovered a few bugs in the program,
which the tech support people there has worked with me on these various
issues. It may take them some time to find the issue, which at times, it
seems as though they don't believe what we are saying, but I'm sure they are
mainly doing that to cover themselves as they probably have heard too many
of such type things from too many customers when there really wasn't an
issue, which I can relate to very easily, especially since I have also
written my own production reporting system within Excel using VBA and
userforms.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

wrote in message
oups.com...
As of late I am experiencing an incredible slow down with Excel 2003
and formatting. I have code in Visual Basic 6 and I am open a
template Excel file. The excel file is filled with records from a SQL
Server data that is often formatted based on certain criteria. None
of the formatting can be off loaded to the template file,
unfortunately. Has anyone else experience this with Excel or the OLE
Excel.Application object from a VB6 application. Any suggestions
would be greatly appreciated for I seem to be all out of options.
Again, I know it's not the database call or anything weird like that
it is definitely the formatting code acting on the Excel.Application
object.

Thanks all

-Thanas