[Solved] Deleting macros

[Hagar Delest]

I have a .xls file that I created several years ago.
When I try to open it using OpenOffice calc, I get a pop-up dialog box saying:
OpenOffice.org 3.2
This document contains macros.
Macros may contain viruses. Execution of macros is disabled due to the current macro security setting in Tools - Options - OpenOffice.org - Security.
Therefore, some functionality may not be available.
[ OK ]

I never intentionally added macros to the document. If there are macros in the document, I do not want them.

I found another post on this forum describing this exact same problem. That post instructs how to delete the unwanted macros, however those instructions did not work for me.
The macros I delete seem to reappear after I save, close, and reopen the document.

Here are my steps to reproduce the problem:
- Double-click on the file foo.xls to open it with OpenOffice calc.
- Dismiss the aforementioned dialog box saying "This document contains macros.".
- Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...
- Click "Organizer..." button
- "Modules" tab: foo.xls -> Standard -> Sheet1: Click "Delete" button.
- "Modules" tab: foo.xls -> Standard -> ThisWorkbook: Click "Delete" button.
- Click "Close" button to close Organizer dialog.
- Click "Close" button to close Macros dialog.
- Save the document.
- Close the document.
- Re-open the document.
=> Get same dialog box saying "This document contains macros.".
- Dismiss the dialog box.
- Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...
- Click "Organizer..." button
- "Modules" tab:
- The following things, which I had previously deleted, have reappeared:
- foo.xls -> Standard -> Sheet1
- foo.xls -> Standard -> ThisWorkbook

I'm using OpenOffice 3.2.1 OOO320m18 (Build 9502)
Windows XP Professional Service Pack 3

[rudolfo]

Do I read this correctly in your list of steps what you have done: You are always saving the file in .xls format?
This might be the problem. Save it as .ods after deleting the modules with the macros. If you still need the file in .xls format. You will have to open the .ods and save it again in .xls format. Depending on the complexity of your file, you might lose some mostly non-relevant parts of your file, do to the closed format of MS Excel (the conversion can't be perfect because it is based on reverse engineering).
If losing something is not acceptable for you, you have to use MS Excel to delete the macros.

[fub5czse]

rudolfo,

Thank you very much for the tip! That solved my problem. I just needed to save in .ods format after deleting the macros, then open that and save back to .xls format again.

[Alexis_Wilke]

I have the same problem, the macro pop-up dialog appears each time... but I only use .ods (although the original was an .xls file...)

So I load the file, go to

Tools -> Macros -> Organize macros -> OpenOffice.org Basic

and in that Dialog I click on "Organizer ..." (whatever you click in the list has no effect from what I can tell).

The first time there was one entry per sheet and one global entry (all "empty" just one flag saying macros are turned on.) I deleted them all, saved, quit OO and restarted it with the same file...

And I'm still getting the pop-up and this time, when I go to the Macros organizer, it's all empty (no Sheet1, Sheet2, etc. to delete.)

I load and save a .ods file only. Anything else I could do to make it go away?

Thank you.
Alexis Wilke

[rudolfo]

If the problems is only that you are nagged by the "File contains macros" dialog you can as well add the location of that file to the trusted file paths and Calc will open that file without asking for confirmation. Of course you should be sure, that the file doesn't contain any harmful macros. But accoriding to your description it doesn't contain macros anymore because you deleted them all.
Or if your file does not contain private data you can upload the file as attachment for others to inspect.

[Alexis_Wilke]

I was trying to get rid of them all and the nag screen saying that there are macros in the file.

The macro I added was in Excel a very long time ago and I really don't need it (i pretty much never used it!) I deleted it when I was still working with Excel but always got that stupid nag screen even when the macro file was mostly empty.

Once I converted to OpenOffice, it continued to generate that nag screen. So I found this thread and noticed that the users were saying that the steps presented here worked for them. Unfortunately, it did not work for me. The macros are gone (and were in the .ods file, not an external file,) but I still get the nag screen. As if there still were some remains of those macro files which I do not see anywhere in the interface...

So I was wondering, what else could I do to prevent the nag screen from anywhere I open this file?!

The trusted location works, but that's not what I had in mind...

Thank you.
Alexis

[MrProgrammer]

This may not be practical in your situation, but you can use the Edit > Sheet > Move/Copy dialog to copy (check that box) all of your sheets to a new spreadsheet. In "To Document" use "- new document -" for the first sheet and then use your new "Untitled N" document for the remaining sheets, if any. Save the new spreadsheet and close both spreadsheets (old and new). Open the new one. Is the annoying message gone? Does spreadsheet look OK? If so, rename old spreadsheet and keep it for a while as a backup. Rename old spreadsheet to the name the old one had, if desired.

This is the techinque for fixing the problem in Excel. Once any macro is created in an Excel file, the file is "poisoned" and appears to have macros in it even if they are all deleted. The only solution is to build a new file and copy the content from old to new.

[Alexis_Wilke]

Hi MrProgrammer,

That worked. Note that you have to do "New Document" before going to "Edit > Sheet > Move/Copy", otherwise you cannot select "Untitled N" (since it doesn't exist yet.)

The one thing that did not copy properly is the grid. Outside of that, I cannot detect any problems (and my file is 103 sheets.)

Thank you!
Alexis

[ChessPub]

Wouldn't it be easier if someone fixed this bug?

[RoryOF]

Volunteers are very welcome:
Get involved with the OpenOffice project

[ChessPub]

Volunteers are very welcome:

LOL if I started altering the OOS code the number of bugs would soon multiply! Maybe Microsoft would even pay me to do this!
Seriously though, this is a strange bug, I've looked around and several people even suggest writing a macro to delete the other macro!

[Alex1]

and my file is 103 sheets.

You can select all sheets: click the last tab, then shift+click the first tab,
then copy the sheets in one operation.

[windowshater]

Save it as .ods after deleting the modules with the macros.

Hello Rudolfo, I thankfully followed your advice, my file is now .ods, but believe me, it still shows the macros warning when I open it.
Just so you know, there must be an additional step somewhere that is required in certain instances. Which step, I don't know of course.

I too don't want "macros" in my calc sheets, "macros" because they aren't real macros anyway: I never saved a macro with those old xls sheets, I still wouldn't know how.

So yeah, it is a bit odd with the macro warnings in oo calc. Unless of course I understand the OP wrong as to what he did and what you corrected. So in my file, it is:
- organize macros
- oo basic macros
- and then there are three main entries:
-- My macros (which certainly aren't mine)
----- ExportImages (this has countless "macros" listed)
----- and Standard (this has no macro listed)
-- OO macros
----- countless suubfolders with macros
-- and MyDomainsinRankingFormat.ods macros
----- sheet 1, 2, and 3 (these I have all deleted)

So, since it didn't help, I went back now and deleted all 25 macros under "ExportImages", I hope it didn't kill some oo functionality(?), but ExportImages appears under "My" macros, not under "OO macros"...

I saved that, reopened the ods, and still says contains macros.

I not gonna ALSO delete all macros under "OO macros" because there are many subfolders each with many macros, and they all are under "OO macros".

Conclusion: It still doesn't work for me, and I cannot consider this as "solved", no.

Or did you mean we really have to go in and delete everything under "oo macros" as well?
Be aware that we cannot shift-click all of them: oo calc only allows individual deletions.

[Zizi64]

The spreadsheet DOCUMENT containd only these macros:

- and MyDomainsinRankingFormat.ods macros
----- sheet 1, 2, and 3 (these I have all deleted)

All of other macros - that you listed - not related to the actual document. The other macros are useful extensions or other features. Do not delete them.
Delete the document related ones - listed under the file name. You must delte the macro code and the Modules and the Standard directory from the document. Then the spereadsheet file will not give you macro alert at the opening.

[windowshater]

Thank you Tibor for getting involved.
"The other macros are useful extensions or other features. Do not delete them." Pity, I deleted them already. However, I haven't noticed a problem yet, maybe what I do doesn't need the macros.

"You must delte the macro code and the Modules and the Standard directory from the document." Ah, interesting, so there is sth else we must do. I understand I deleted only 1/3 ("the macros"). What do you mean with the other two parts, "the Modules" and "the Standard directory", I can't find either of those in any menu?
And it's not under "Organize macros", in my installation (OO 4.1.4).
In Options, under OO Calc I don't see them either?

Could you maybe add your explanation to Rudolfos helpful first part, then anyone will find everything on this one page?
(and then it really may be solved, lol)

[Zizi64]

Pity, I deleted them already. However, I haven't noticed a problem yet, maybe what I do doesn't need the macros.

You know it...

What do you mean with the other two parts, "the Modules" and "the Standard directory", I can't find either of those in any menu?
And it's not under "Organize macros", in my installation (OO 4.1.4).

The macro code (the Function-s and the Sub-s) are located in Modules. One TAB with some macro code (what you can see in the IDE) means a Module. You must delete the TAB-s (the Modules) from the document.

The Modules are located in a Directory. The default directory of an .ODF type document is the directory, named "Standard". The working of the directories: Only the Standard directory will be loaded automatically (into the memory), when you open a document containing some macros.

The documents that was created in MS Office will usually inherit more than one Directory. One for the sheets, andd more... You must delete all of them from the document.

but ExportImages appears under "My" macros, not under "OO macros"...

-- My macros (which certainly aren't mine)

Yes those are/was YOUR macros. They are the Macros that you had wrote, or you had installed (as an extension or by other way). The "ExportImages" is an useful extension, what helps you exporting the images from the document. You can download and install it again - if you need it: https://extensions.openoffice.org/en/pr ... ort-images
The MyMacros is related to the office suite software, but not to an actual document.

Important:
Macros that was installed by the main installer are located in the Directory named "OpenOffice / LibreOffice Macros". Those macros are very important. Here are the language tools, and many more important things. Never delete them! (You can add/remove one or more of them by rerunning of the Installer.

[windowshater]

Thank you for your reply.
"One TAB with some macro code (what you can see in the IDE) means a Module. You must delete the TAB-s (the Modules) from the document.
The Modules are located in a Directory. The default directory of an .ODF type document is the directory, named "Standard""

Do you mean, with the file open,
Tools
Macros
Organize Macros
OpenOffice Basic
> There I have already deleted everything under "My Macros", and left "OpenOffice Macros" untouched. And under the branch named with the document, there is one sub branch "Standard", and I also deleted everything there (in my case, sheet1, sheet2, ThisWorkbook).

Or do you mean in File Explorer,
C
ProgramFiles(x86)
OpenOffice4
presets
basic
Standard
> That's the only folder named "Standard" (I looked), and at the moment there are 3 files:
script.xlb
Module1.xba
dialog.xlb

Should I delete those 3? I doubt that's what you mean, because you write "Macros that was installed by the main installer are located in the Directory named "OpenOffice / LibreOffice Macros". Those macros are very important"

and I don't have that folder anywhere, the main folders under OpenOffice4 are
help
presets
program
readmes
share
Opera

I had actually tried to copy paste all cells of each sheet into a new ods document, but the macro warning keeps coming. None of the cells copied would suggest it holds a macro though.

So basically I am unsure what you mean with "One TAB with some macro code (what you can see in the IDE) means a Module. You must delete the TAB-s (the Modules) from the document."

[Zizi64]

Do you mean, with the file open,
Tools
Macros
Organize Macros
OpenOffice Basic
> There I have already deleted everything under "My Macros", and left "OpenOffice Macros" untouched. And under the branch named with the document, there is one sub branch "Standard", and I also deleted everything there (in my case, sheet1, sheet2, ThisWorkbook).

Yes.

Or do you mean in File Explorer,
C
ProgramFiles(x86)
OpenOffice4
presets
basic
Standard
> That's the only folder named "Standard" (I looked), and at the moment there are 3 files:
script.xlb
Module1.xba
dialog.xlb

Should I delete those 3? I doubt that's what you mean, because you write "Macros that was installed by the main installer are located in the Directory named "OpenOffice / LibreOffice Macros". Those macros are very important"

These macros are not located inside a document. You want to delete the macros from a specific document.

[windowshater]

Not to bother you, hopefully someone else has an idea too, thanks for all your answers

Just to clarify: I said I DID delete all macros there where you confirmed "Yes" it's the right place. Correct?

But it really really STILL shows "macros" warning when opening that ods document (spreadsheet file).

I appreciate if it's hard to believe, but I am telling the truth.

[Zizi64]

Just to clarify: I said I DID delete all macros there where you confirmed "Yes" it's the right place. Correct?

But it really really STILL shows "macros" warning when opening that ods document (spreadsheet file).

Please upload that file here. Delete all of sensitive contents before.

[windowshater]

Thank you. Why did you now change your name from Tibor to Zizi64? Did I do sth wrong?

Instead of uploading it to the world..., could I send a dropbox link to you just in case you'd like to find the reason for "contains macros" out of curiosity or OO reasearch reasons or whatever?

(this forum software may even allow private messages, I would check once you reply

[robleyd]

Thank you. Why did you now change your name from Tibor to Zizi64? Did I do sth wrong?

His forum user name always has been and is Ziz64 - still - and his actual name is as shown in his signature; nothing has changed

this forum software may even allow private messages

Indeed - see the PM icon next to the user name on the right of posts, or use the messaging option in the User control panel: control panel is available from the link at the top of each page in the forum. If your spreadsheet is less than 128 Kb it can be attached to a PM

[windowshater]

Good to know, thanks! Politeness forbids me to wildly send anyone a pm without them personally saying "yes", I can't do it just because the mod kindly points out it is technically possible

And no, if you read my reply "Thank you Tibor for getting involved." you realize I address such kind respondent by name (it's polite) picked up from below a member's pic, thus it was there, no need to argue with that, sir.
I see I should put a pic up as well... one that fits my login name though

I have deleted login details (is the file about), and surprisingly it *still* shows "contains macros" (I thought it wouldn't now), so yeah I *could* pass on that file on in theory. If someone wants, personally
I am not smart enough with spreadsheets to see WHY/WHERE are macros. Honestly I prefer to work with csv files anyway, as in "see what you get", raw data. But I shouldn't say that, this is about the great OO CALC! which really is great.

[Zizi64]

I am not smart enough with spreadsheets to see WHY/WHERE are macros.

Why are macros in a document? Because somebody created them. Never will be created a macro in a new, empty ODF type document automatically, if you want not create any macros.

I do not know anything about the newest MSOffice, but I suppose if it works similarly.

[windowshater]

I take I shall not send the file then, Zizi?

Either way, you have too much confidence in my skills: I could not under any circum stances make a macro in Excel, in no past life, no. And I doubt in my remaining short period here I will learn that. Cause I DO NO NEED MACROS in ods or excel either

[Zizi64]

I take I shall not send the file then, Zizi?

Either way, you have too much confidence in my skills: I could not under any circum stances make a macro in Excel, in no past life, no. And I doubt in my remaining short period here I will learn that. Cause I DO NO NEED MACROS in ods or excel either

The macros was created by some user activity in your file. That was you or other people, I do not know it...

Just a final tip:
Copy all of the contents (but not the macros) into a new, empty ODF type file, and save it.

[windowshater]

I understand you, no problem, okay so then just a final reply from me too: I had tried that too, of course

Since I can't see in which cells some "macro" is hiding, no attempt of mine was successful, I am afraid (excluding different rows/columns each time)

Now I suggest someone with authority does the correct thing: Delete the word "Solved" from this thread. The recommendation above does NOT solve it for everyone.
(let's leave this in, for posterity, shall we?)

[robleyd]

From your first post, I gather your file was originally an Excel format file? If not, please ignore the rest of this post; if so, did you try the suggestion by MrProgrammer above ? I'll copy it here as it is a long scroll up:

This may not be practical in your situation, but you can use the Edit > Sheet > Move/Copy dialog to copy (check that box) all of your sheets to a new spreadsheet. In "To Document" use "- new document -" for the first sheet and then use your new "Untitled N" document for the remaining sheets, if any. Save the new spreadsheet and close both spreadsheets (old and new). Open the new one. Is the annoying message gone? Does spreadsheet look OK? If so, rename old spreadsheet and keep it for a while as a backup. Rename old spreadsheet to the name the old one had, if desired.

This is the techinque for fixing the problem in Excel. Once any macro is created in an Excel file, the file is "poisoned" and appears to have macros in it even if they are all deleted. The only solution is to build a new file and copy the content from old to new.

Apparently that worked for someone who had an Excel file, converted to AOO and stil got a nag despite having removed his macro via Excel.

[windowshater]

THAT worked, Robleyd!
I copied the ENTIRE SHEETS from the .ods to new .ods file (had no old xls version)
closed both
reopened new file
No "macro" warning

The ods now is 27kb as compared to before 20kb, suggesting that those earlier xls "macros" consumed negative space, so "poisened" xls is....?
Normally when DELETING sth you know files get SMALLER, lol.
No idea why copying entire sheets works, but cells, columns, rows, not. VERY ODD, the opposite would make sense.

Whatever. THIS is the solution, thank you

[Zizi64]

Why did you now change your name from Tibor to Zizi64? Did I do sth wrong?

I never changed my name. My real given name is Tibor since 1964, and the forum nickname is Zizi64 since the registration date: 2010 May 26, 7:55 am.
Maybe you missed something, or you were careless a littlebit...

The ods now is 27kb as compared to before 20kb, suggesting that those earlier xls "macros" consumed negative space, so "poisened" xls is....?

Maybe it depends on your locale settings. The localized names of the formulas have different character count.
But it is a guessing only - without seeing your files.

Since I can't see in which cells some "macro" is hiding,

The cells can not contain macros. They can contain "macro callings" only. If only one cell contains a macro calling in your file, the you MUST know who wrote that formula into the cell.

Now I suggest someone with authority does the correct thing: Delete the word "Solved" from this thread. The recommendation above does NOT solve it for everyone.

NEVER continue a topic marked as "Solved". Make a new topic for your problems, even it is similar to other topics. This topic was Solved for the original poster, and for the users those have some experience in the usage of the Calc.
Please read the Survival Guide of this Forum: https://forum.openoffice.org/en/forum/v ... f=50&t=166.