QUERY: Excel's SaveAs so slow..

[KevinGPO]

Just wondering if anyone knows about Excel's SaveAs command?

I load up a Excel file via VNC on a server on the otherside of the world. I
got a macro which does a SaveAs command to a network drive which is my local
HD. It takes over 12 minutes to do a save of a file only 600kb. Is this
normal?

The macro however does run a lot of formating and data parsing/entry from an
XML file, so I guess a lot of memory would be consumed. Maybe if I free up
the memory before the save? But in Excel you cannot free up memory easily.
There's no memory management commands, and no garbage collection.

Would it be quicker to issue a copy or maybe move command? Because I used
Windows Explorer to copy the 600kb excel file from the server to my local
drive and it took seconds! So maybe it's Excel '97 really slow SaveAs
command?

 

[KevinGPO]

I realised that my excel file template contains quite a massive
macro/script. After running the file it SaveAs a copy of itself (after it
has ran the script, importing data, etc.). It won't need 90% of the
macro/script anymore. How is it possible to remove/delete the class modules
within the Excel file in the new "saved" file?

 

[AnExpertNovice]

Listen to others first.

From your test it seems as if the formatting is what is taking a long time.
I'm not sure what your level of expertise is, so please forgive me if I am
assuming you are a new to Excel programming. Think of it as we are helping
others.

Formatting is very slow depending on how it is being accomplished. If
formatting is being done one cell at a time it will be much too slow. In a
response just made the following code was recorded.
Sub Macro1()
With Selection.Font
.Name = "Courier New"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 14
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

That would be a very slow command if executed as is on a cell-by-cell basis.
Since there are only two changes from the default formatting
(Interior.ColorIndex and FontStyle). It would be much faster to set the
entire range at one time. At least set the entire range to the default then
set only the two changes to specific cells.


Also
With Selection.Font
.Name = "Courier New"
.FontStyle = "Bold"
End With

is faster than
Selection.Font.Name = "Courier New"
Selection.Font.FontStyle = "Bold"

To reiterate, selecting all cells to be given the same formatting and
changing them at one time is much faster than changing them one at a time.