Changing filename causes major slowdown, any help appreciated

[Dom_Ciccone]

I have built an application that produces separate workbooks of financial
data for each of our contracts on a monthly basis, approximately 300
workbooks per month. This application runs fine and very fast. However, I
need to make a couple of changes to it. When I save the file under a new
name (even just saving an unaltered copy) as a backup, the saved copy runs
very slowly.

I've stepped through the code and it appears to be slowing down on the
Application.CalculateFull command. However the calculations and formulae are
in a different workbook and should not be affected by me changing the
application filename.

Can anyone explain why this is happening and/or suggest a solution please?
This is a very urgent piece of work.

Thanks

 

[Norman Jones]

Hi Dom,

'--------------
have built an application that produces separate workbooks of financial
data for each of our contracts on a monthly basis, approximately 300
workbooks per month. This application runs fine and very fast. However, I
need to make a couple of changes to it. When I save the file under a new
name (even just saving an unaltered copy) as a backup, the saved copy runs
very slowly.

I've stepped through the code and it appears to be slowing down on the
Application.CalculateFull command. However the calculations and formulae
are
in a different workbook and should not be affected by me changing the
application filename.

Can anyone explain why this is happening and/or suggest a solution please?
This is a very urgent piece of work.
'---------------

The CalculateFull method forces a recalculation of all
open workbooks.

Additionally, if the calculations are in a different workbook,
why do you need to recalculate the backup copy?

 

[Joel]

Are you closing the work,books after you are done with each one?

 

[Dom_Ciccone]

Yes I am closing them. Perhaps I didn't explain clearly enough.

The application workbook contains querytables linking to a SQL Server
database.

The application opens a template workbook, feeds in the contractors code and
then the template file is recalculated (to force all the VLookups to adjust
to the contractors code. These VLookups get their data from a combination of
the contractors code and named ranges in the application workbook). This
file is then saved under a new filename and closed, before the whole process
begins again with the next contractor code.

The problem is that the contractor codes have changed from 3 digit to 5
digit, so I have to make various changes in the VBA. I therefore have 2
choices: Either make changes to the existing file and save it as a new file
(which runs but with the slowdown issue) or take a backup of the existing
version and make my changes in the original. (I haven't tried this method,
because the backup copy I take also suffers the same slowdown issue and if
anything goes wrong I do not wish to replace the original with a faulty
backup).

It's very frustrating that if I open the application that works, perform a
Save As with a new filename and then, without even closing the file, run
it...it still suffers from the slowdown, where a moment earlier it didn't.

 

[Joel]

Look at VBA Project window and see what objects are still open.

When I open multiple files I use only one object that I use for every file.
Not open a new object for each file. the VBA Project window may help give
you a clue to the source of the problem.

 

[Dom_Ciccone]

Thanks Joel, I'll try that.

It's very confusing that it works fine until I change the filename.