Long list of Document Objects building up [closed]

asked 2013-09-28 19:28:01 +0100

jheath gravatar image

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 ... (more)

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-05 00:21:47.609961