NEW Worksheets - Limitation on Creating and Deleting (-fes)

[Oreo1Coco2]

I have developed a VBA analyses program that is used over and over to pull
data from an Access Database and then analyze and graph the data. The
program can create up to 50 worksheets with Graphs during each run, and then
deletes these worksheets when a new run is started.
Over the course of many runs of this VBA program, EXCEL will give me an
error that it cannot create (or copy) a new worksheet even though the excel
workbook only contains maybe 5-10 worsheets at the time. However, if i close
the workbbok and quit EXCEL, the macros work fine to create new worksheets as
mentioned above.
It thus appears that there is a limitation to the way EXCEL maps the
creation and deletion of worksheets within a session, and after a certain
number of worksheets have been created/and then deleted, EXCEL will refuse to
create further worksheets until the Workbook is closed and/or Excel is
restarted.
Is there a way to programmatically re-set the tables EXCEL uses to keep
track of created/deleted workbooks during a session (e.g.- when the new data
grabbing VBA program restarts).

Thanks for your help

 

[Jim Cone]

Try setting font AutoScaling on each chart to False (when you create it)...
.ChartArea.AutoScaleFont = False
--
Jim Cone
Portland, Oregon USA



"Oreo1Coco2"
<[email protected]>
wrote in message
I have developed a VBA analyses program that is used over and over to pull
data from an Access Database and then analyze and graph the data. The
program can create up to 50 worksheets with Graphs during each run, and then
deletes these worksheets when a new run is started.
Over the course of many runs of this VBA program, EXCEL will give me an
error that it cannot create (or copy) a new worksheet even though the excel
workbook only contains maybe 5-10 worsheets at the time. However, if i close
the workbbok and quit EXCEL, the macros work fine to create new worksheets as
mentioned above.
It thus appears that there is a limitation to the way EXCEL maps the
creation and deletion of worksheets within a session, and after a certain
number of worksheets have been created/and then deleted, EXCEL will refuse to
create further worksheets until the Workbook is closed and/or Excel is
restarted.
Is there a way to programmatically re-set the tables EXCEL uses to keep
track of created/deleted workbooks during a session (e.g.- when the new data
grabbing VBA program restarts).

Thanks for your help

 

[Ritwik Shukla]

If you are using OLE any where try resetting that object with

Set xyz = Nothing