I have a Calc document that runs a BASIC macro when it first opens up that deletes any existing sheet named Accounts, and then it loads up the latest accounts into a new Accounts sheet from an external CSV file. From all appearances in the Calc document, the macros are working fine, but when I look at the document in the BASIC IDE, every time I run this code it adds another Document Object that I can’t seem to get rid of. Let me see if I can walk you through what I see.
As of right now, I have a Transfer.ods file which, in the BASIC IDE, has a “Standard” library container, and under the Standard library container there are several libraries: “Document Objects”, “Forms”, “Modules”, “Class Modules”. The Modules library has a Module1 module that contains my code. (I actually have some of my code in a separate global container right now, but I tried moving the code to the same document and I had the same problem.) The two Forms and Class Modules libraries are empty, but the “Document Objects” library is the problem. I currently have 31 items in it, labeled “Sheet1” to “Sheet31 (Accounts)”. But in the Calc document it just shows 3 sheets: Transfer, Journal and Accounts. Now if I close the document and open it again, it will run my initialization code, and eventually gets to this:
' if Accounts sheet exists already, delete it
If oDoc.Sheets.hasByName("Accounts") Then
oDoc.Sheets.removeByName("Accounts")
End If
And the “Accounts” sheet is removed in the document, but the “Sheet31 (Accounts)” is still sitting in the “Document Objects” library. Then I load the CSV file, and copy some data from the main sheet. At some point in this process the item in “Document Objects” changes name to “Sheet31 (Journal)” - there isn’t an Accounts sheet any more after all! - but there is still a “Sheet 30 (Journal)”, “Sheet29 (Journal)”, etc. But there are only 6 or 7 that are still called “(Journal)”; the rest are just called “Sheetx”.
Then the code creates a new Accounts sheet to copy that data into:
' add Accounts sheet after Journal sheet
journalSheet = oDoc.Sheets.getByName("Journal")
oDoc.Sheets.insertNewByName("Accounts", journalSheet.getRangeAddress().Sheet + 1)
And at this point there is now a “Sheet 32 (Accounts)” in the “Document Objects” library. Then I paste the CSV data into the new sheet, and everything at the document level looks fine. But in “Document Objects” I now have one more Sheet!
If I try to open any of these “Document Objects” sheets, it puts a whole bunch of “Sheetx” tabs at the bottom of the IDE. Some of them contain just the following:
Rem Attribute VBA_ModuleType=VBADocumentModule
Option VBASupport 1
Some of them contain a small BASIC stub with just an empty Sub Main. Those tend to show up under the Modules library when I open them, but if I delete them, the “Sheetx (Journal)” is still listed under the “Document Objects”.
So the questions are: Why am I creating all of these “Document Objects” items, and how do I stop doing that? And how can I get rid of (clean up) all of these “Document Objects” that have alreay been created? They show up under the macro organizer, but when I select one, the Delete button is greyed out. I try deleting the 2 VBASupport lines, but that doesn’t seem to help.
Thanks for any help you can give me!
Jeff