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?
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?