Too long to save an Excel file

[sk005]

Hi, I wrote an Excel/VBA application using a lot of modules and class
modules. the file size of this application tempate is 6.8M. It takes just
over two minute to save to network share and above a minute to save locally.
Is there any way to improve the saving time? I tried to implement the
Micosoft solution of saving it locally (c:\temp ) mostly and background copy
to the share drive and then re-opening newly coped file from the share but
the best I can hope for is about a minute. Is there anyway I can improve the
saving time significantly?

Thanks in advance.
sk005

 

[JLGWhiz]

You could try disc cleanup and defragmentation. That might help some, but I
doubt that is the real cause. I have had files of about 1 meg that took
longer to save than files of three or four meg and have never figured out
exactly why, although I suspect it is a protocol problem. I have no idea
how Excel writes files to disc but it probably has a certain protocol and if
the file contains formulas it does one thing, if it has code it does another
and if it has both the it does something else, but I have no idea which
causes what and how the formatting decisions are made to write the files and
index them in the FAT. I have just learned to accept the fact that some do
better than others.

 

[JLGWhiz]

One other thing. Some antivirus programs will scan your files before
saving. Check to see if you can turn that off if you have that type of
security software installed.

 

[Jim Cone]

My suggestion is to reduce the file size. Some stuff to consider...

1. Delete (not clear) all rows/columns below and to the right of the last
cell with data.
2. Run a count of objects on each sheet.
I've seen 1000 copies of the same button stacked one on top of the other.
3. Eliminate the "pretty" cell formatting and any charts you don't actually need.
4. Clean out your regular code modules by...
-copying and pasting the code to Notepad
-delete the modules and replace them with new ones
-copy and paste the code back from Notepad
5. If you are using userforms, check the list of controls against the actual
ones in use. Several times I have found unused/forgotten controls
positioned beyond the actual form width or height.
6. Buy low, sell high. <g>
--
Jim Cone
Portland, Oregon USA




Hi, I wrote an Excel/VBA application using a lot of modules and class
modules. the file size of this application tempate is 6.8M. It takes just
over two minute to save to network share and above a minute to save locally.
Is there any way to improve the saving time? I tried to implement the
Micosoft solution of saving it locally (c:\temp ) mostly and background copy
to the share drive and then re-opening newly coped file from the share but
the best I can hope for is about a minute. Is there anyway I can improve the
saving time significantly?
Thanks in advance.
sk005

 

[JLGWhiz]

Like I said, I have no expertise in the mechanics of the save activity. Jim
had some suggestions that looked pretty good for tightening up your code and
file structure. I don't know that the number of modules would make that
much difference, but stacking the controls as Jim pointed out certainly
would. That is if it actually saved the same control for each module that
had a call for it. Again, I do not know the technical protocol for the save
activity, so your guess is as good as mine in that case.

 

[Peter T]

If a significant proportion of the file size is the VBA code I'd strongly
recommend you split the code and data into separate workbooks.

Regards,
Peter T

 

[sk005]

Thanks very much for your suggestions.

Hi Peter T: Could you please elaborate a bit more on your suggestion? Are
you suggesting to make an Add-in of the code? Do you have any examples I can
refer to?

Thanks in advance.
sk005

 

[Peter T]

Whilst there are no hard rules, in general with large overall projects it's
a good idea to split processing (code), logic (formulas), data, input and
output (not necessarily a separate file for each). If you have serious work
to do there are excellent examples in the book "Professional Excel
Development" by Bullen, Bovey & Green. I think I saw it has been updated for
2007.

The code could indeed be in an Addin. You could install in the Addin manager
which will cause it to load at startup, or you might only want it to load
if/when your data workbook opens. Maybe you have several data workbooks,
something like the following could go in each one -

Sub OpenEventStuff() ' Workbook_Open or Auto_Open
Dim sPath As String
Dim wb As Workbook
Const cCodeFile As String = "vvvTmp.xls"

sPath = "C:\Documents and Settings\Owner\My Documents\"

On Error Resume Next

Set wb = Workbooks("myAddin.xla")
On Error GoTo errH

If wb Is Nothing Then
Set wb = Workbooks.Open(sPath & cCodeFile)
End If


Exit Sub
errH:
' errer handler code

End Sub

I have no idea what your code does but you might want a small amount of code
in the data workbook to liase with the addin. You might ant to set a
reference in the data workbook to the addin, if you do best to rename the
default VBAProject in the addin.

Not knowing what you have, but say you have a small amount of worksheet
cells which are for manual data entry, instead of an addin you might combine
the code and the entry into a normal workbook, then have the code validate
the data and save it to a data file. I could go on with many more ideas but
it all depends on what you are doing.

Regards,
Peter T