workbook close delay

M

Mike

I have a code that is perplexing me and I am hoping one of the experts
can help. I have a main workbook that contains all the subroutines
and a main menu. this workbook is opened at the beginning of the
business day. Through the main menu, the user will open and close
various data files depending on the project needs, however, excel and
the main program will stay open all day.

The issue that I am seeing is the workbook("filename").close false
step executes very quickly the first time, but it takes longer and
longer each time it is evoked from the code. prior to opening a new
data file, a subroutine is called to clear the existing data. In that
routine is the command to close the current data file.

Here are the parts of the code

in a separate routine
sub open_data_file()
....
Set g_wbData = Application.Workbooks.Open(strDataFilePath &
strDataFileName)
...
end sub

sub clear_data()
...
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
debug.print timer

g_wbData.close false '<---- this step takes longer each time
it is executed

debug.print timer
With Application
.EnableEvents = true
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With
...
end sub

I have used a timer to monitor the execution time of the single step.
it starts at ~0.15 seconds and increases by 0.25 seconds each time a
data file is opened and the clear_data routine is subsequently used.
After 20 or so repetitions the delay gets unacceptable. If I close
excel and restart the main program the execution starts at 0.15
seconds again.
Any suggestions? Is there some data that is saved from the closed
workbook that is using memory?
 

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