Excel save takes 18 times longer than Windows Explorer copy - why?Options

C

Chrisso

Hi All

I have an Excel system with about 10 sheets and loads of VBA modules
and classes.


It resides on a network at my company's headquarters.


When I copy the file from my desktop to the server using Windows
Explorer drag and drop it takes about 6 seconds.


However if I open the Excel file from the server from my PC and save
it using Excel the save time is 90 seconds.


Is this normal? My users are complaining about the time it takes to
save after editing. It also means that they are not saving often and
are risking losing changes. Any thoughts?


If the length of my save is normal then what should I do to reduce
the
size of my file for a quicker save? How much does VBA code add to the
file size or should I be looking at the size of each worksheet?


Cheers
Chrisso
 
S

Simon Lloyd

When you simply drag and drop Excel does not need to make any
calculations, however when the workbook is opened/closed calculations
may take place, try setting calculation to manual in the beforesave
event or beforeclose event, like this:

With Application
..Calculation = xlManual
..MaxChange = 0.001
..CalculateBeforeSave = False
End With don't forget to turn it back on in the open event!

Chrisso;628211 said:
Hi All

I have an Excel system with about 10 sheets and loads of VBA modules
and classes.


It resides on a network at my company's headquarters.


When I copy the file from my desktop to the server using Windows
Explorer drag and drop it takes about 6 seconds.


However if I open the Excel file from the server from my PC and save
it using Excel the save time is 90 seconds.


Is this normal? My users are complaining about the time it takes to
save after editing. It also means that they are not saving often and
are risking losing changes. Any thoughts?


If the length of my save is normal then what should I do to reduce
the
size of my file for a quicker save? How much does VBA code add to the
file size or should I be looking at the size of each worksheet?


Cheers
Chrisso


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
 
J

Joe User

Simon Lloyd said:
try setting calculation to manual in the beforesave
event or beforeclose event, like this:

With Application
Calculation = xlManual
MaxChange = 0.001
CalculateBeforeSave = False
End With

But don't forget the dots before property names; to wit:

With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With

And should MaxChange really be part of the process?

We do see it when we record a macro while setting the manual calculation
option. But then again, we also see PrecisionAsDisplayed being set, yet we
are wise enough to recognize that as extraneous.


----- original message -----

Simon Lloyd said:
When you simply drag and drop Excel does not need to make any
calculations, however when the workbook is opened/closed calculations
may take place, try setting calculation to manual in the beforesave
event or beforeclose event, like this:

With Application
Calculation = xlManual
MaxChange = 0.001
CalculateBeforeSave = False
End With don't forget to turn it back on in the open event!

Chrisso;628211 said:
Hi All

I have an Excel system with about 10 sheets and loads of VBA modules
and classes.


It resides on a network at my company's headquarters.


When I copy the file from my desktop to the server using Windows
Explorer drag and drop it takes about 6 seconds.


However if I open the Excel file from the server from my PC and save
it using Excel the save time is 90 seconds.


Is this normal? My users are complaining about the time it takes to
save after editing. It also means that they are not saving often and
are risking losing changes. Any thoughts?


If the length of my save is normal then what should I do to reduce
the
size of my file for a quicker save? How much does VBA code add to the
file size or should I be looking at the size of each worksheet?


Cheers
Chrisso


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread:
http://www.thecodecage.com/forumz/showthread.php?t=174829

Microsoft Office Help
 
C

Chrisso

When you simply drag and drop Excel does not need to make any
calculations, however when the workbook is opened/closed calculations
may take place, try setting calculation to manual in the beforesave
event or beforeclose event, like this:

With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With don't forget to turn it back on in the open event!

Chrisso;628211 Wrote:




I have an Excel system with about 10 sheets and loads of VBA modules
and classes.
It resides on a network at my company's headquarters.
When I copy the file from my desktop to the server using Windows
Explorer drag and drop it takes about 6 seconds.
However if I open the Excel file from the server from my PC and save
it using Excel the save time is 90 seconds.
Is this normal? My users are complaining about the time it takes to
save after editing. It also means that they are not saving often and
are risking losing changes. Any thoughts?
If the length of my save is normal then what should I do to reduce
the
size of my file for a quicker save? How much does VBA code add to the
file size or should I be looking at the size of each worksheet?
Cheers
Chrisso

--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=174829

Microsoft Office Help- Hide quoted text -

- Show quoted text -

Thanks - i will give it a try.

Chrisso
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top