Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.vba
|
|||
|
|||
QUERY: Excel's SaveAs so slow..
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? |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.vba
|
|||
|
|||
QUERY: Excel's SaveAs so slow..
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? "KevinGPO" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.sdk,microsoft.public.excel.worksheet.functions,microsoft.public.office.developer.vba
|
|||
|
|||
QUERY: Excel's SaveAs so slow..
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. :D 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. -- My handle should tell you enough about me. I am not an MVP, expert, guru, etc. but I do like to help. "KevinGPO" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QUERY & HELP: so slow executing VBA code... :S | Excel Worksheet Functions | |||
AHHH! Again | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
File saves ok, SaveAs VERY slow | Excel Discussion (Misc queries) | |||
SaveAS macro Query | Excel Discussion (Misc queries) |