Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEW Worksheets - Limitation on Creating and Deleting (-fes)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEW Worksheets - Limitation on Creating and Deleting (-fes)
Try setting font AutoScaling on each chart to False (when you create it)...
.ChartArea.AutoScaleFont = False -- Jim Cone Portland, Oregon USA "Oreo1Coco2" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEW Worksheets - Limitation on Creating and Deleting (-fes)
If you are using OLE any where try resetting that object with
Set xyz = Nothing "Oreo1Coco2" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating new worksheets and appending data from multiple worksheets. | Excel Programming | |||
creating worksheet with code - limitation on number of worksheets? | Excel Programming | |||
VBA / Macro for creating new worksheets and new columns from existing worksheets | Excel Programming | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming |