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


O

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
 
Ad

Advertisements

J

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
 
Ad

Advertisements

R

Ritwik Shukla

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

Set xyz = Nothing
 

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