[Solved] Macros lost

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Dworfus
Posts: 4
Joined: Tue May 08, 2018 1:47 pm

[Solved] Macros lost

Post by Dworfus »

Hi
after I save my ods files in xls format all my macros are lost.
Meanwhile I understand that this action deletes all macros. I thought it will only delete the macros for the new file - bad luck

Is there any way to recover the lost macros on Win10 ?
Last edited by Dworfus on Wed May 09, 2018 12:52 am, edited 1 time in total.
OpenOffice 4.1.3 on Windows 10
User avatar
Hagar Delest
Moderator
Posts: 32628
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Macros lost

Post by Hagar Delest »

Don't you have a backup (see [Tutorial] The OpenOffice User Profile) or a copy of the file in native ODF (.ods)?
I don't remember if the macros are deleted or just commented as text (hence still available in the MS IDE).
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Dworfus
Posts: 4
Joined: Tue May 08, 2018 1:47 pm

Re: Macros lost

Post by Dworfus »

@Hagar
No, I do not have a backup. The problem seems to be that the IDE stores them in a central place in your user profile (C:\Users\<name>\AppData\Roaming\OpenOffice)
Due to that it looks like there is no easy recovery. Even if you back the ODS files it would not help.
My last hope is that there is some kind of system mechanics that stores the macros maybe somewhere else also ... but hope fades away ...
(who is designing a product that deletes essential information globally when you save a file locally "§$&% )
OpenOffice 4.1.3 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macros lost

Post by RoryOF »

Dworfus wrote: (who is designing a product that deletes essential information globally when you save a file locally "§$&% )
Ask rather "who saves a file in a non-native format and doesn't read the manual?"


Use PhotoRec and see if you can recover any temporary files, which may or may not contain the macros.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macros lost

Post by Zizi64 »

The API related macros will not work under the Excel software. Therefore it is not worth it to save your files into a foreign, obsolete file format.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macros lost

Post by FJCC »

Dworfus wrote:@Hagar
The problem seems to be that the IDE stores them in a central place in your user profile (C:\Users\<name>\AppData\Roaming\OpenOffice)
I am confused. If the macros were saved in the user profile ( seen as My Macros in the IDE), then saving the file in xls format would not affect them at all. If the macros are saved in the document, then saving as xls seems to lose them. At least it did in a quick test I just ran.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Dworfus
Posts: 4
Joined: Tue May 08, 2018 1:47 pm

Re: Macros lost

Post by Dworfus »

FJCC wrote:
Dworfus wrote:@Hagar
The problem seems to be that the IDE stores them in a central place in your user profile (C:\Users\<name>\AppData\Roaming\OpenOffice)
I am confused. If the macros were saved in the user profile ( seen as My Macros in the IDE), then saving the file in xls format would not affect them at all. If the macros are saved in the document, then saving as xls seems to lose them. At least it did in a quick test I just ran.
that is the point. All macros seems to be stored in
C:\Users\<name>\AppData\Roaming\OpenOffice\4\user\basic\...
if you export a ODS file to XLS format this directory seems to be wiped
OpenOffice 4.1.3 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macros lost

Post by Zizi64 »

C:\Users\<name>\AppData\Roaming\OpenOffice\4\user\basic\...
if you export a ODS file to XLS format this directory seems to be wiped
It never happened in my older or newer AOO / LO versions.
Can you upload the macro code and an ODF type sample file here (what uses that macro)?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macros lost

Post by FJCC »

1. Basic macros are not necessarily stored in My Macros. You can choose to store them there or within the document. Each possibility makes sense under certain conditions.

2. Saving as xls does not erase the contents of My Macros. That would be horrible behavior and I just confirmed that it does not happen on my system. I believe you when you say you lost your macros, but the mechanism is not an intended behavior of OpenOffice.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Dworfus
Posts: 4
Joined: Tue May 08, 2018 1:47 pm

Re: Macros lost

Post by Dworfus »

Firstly - I cannot upload the files here because it contains some 'secret' data

Secondly I am not sure which activity finally destroyed the macro. It was saved in the IDE under My Macros. All what I can say is that it was there until I exported the sheets to XLS.

But just now I made a test where I created some macro content and exported the sheet to XLS - this time the macro has not been destroyed.
So whatever it is it seems not to be trivial. But it may certainly be connected to the export.
OpenOffice 4.1.3 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macros lost

Post by Villeroy »

Create a new document.
Save a Basic module embedded in the new document.
Save it in any foreign file format.
Code still there.
Reload the document.
Code is gone (of course).

You must never ever save your work in foreign file formats, no matter which category of editing software you are using with your pictures, movies, sounds, CAD models, documents.
Always store your work in the native format of the program you are using.
When there is a requirement that forces you to convert a document to some foreign file format, then you may create a copy in foreign file format while keeping your working copy in the native format.
When you want someone to have a look at your work, you convert pictures to gif, png, jpeg, convert movies to mp4, sounds to mp3 and you convert printable stuff such as office documents to pdf. These are file formats for final output that can be viewed on all devices.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Macros lost

Post by John_Ha »

By default macros are stored in the User Profile in C:\Users\xxxxxx\AppData\Roaming\OpenOffice\4\user\basic\Standard. If you replace the User Profile you lose them.

See [Tutorial] Differences between Writer and MS Word files for differences between MS and AOO files - it applies to spreadsheets too.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
KarenRogers
Posts: 7
Joined: Wed May 16, 2018 7:01 pm

Re: [Solved] Macros lost

Post by KarenRogers »

I am having the same problem. Record macros, save them, organize them under "My Macros" and the default module. Close Open Office, open Open Office, macros are gone.

I have made a lovely drop-down menu on the standard tool bar with the macros, and that remains, but OO tells me that the macros are gone.

I have already set the macro security (under options, security, macro security) to "low - not recommended" but that didn't help either.
Open Office 4.1.5 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Macros lost

Post by RoryOF »

In which format do you Save? Best to use OO's native ODF formats.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Macros lost

Post by Zizi64 »

I have already set the macro security (under options, security, macro security) to "low - not recommended" but that didn't help either.
It is enough to set the macro security to Medium, when you store the macro into the document, but not into the MyMacros.
Not needed modify the security level, when you save the macro into MyMacros -(- Standard - Module 1).
Last edited by Zizi64 on Wed May 16, 2018 9:32 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
KarenRogers
Posts: 7
Joined: Wed May 16, 2018 7:01 pm

Re: [Solved] Macros lost

Post by KarenRogers »

thanks.
Open Office 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Macros lost

Post by Villeroy »

Global macros (those under "My Macros" or "OpenOffice Macros") need to be installed explicitly if you trust the publisher of that code.
Document macros are a security risk. Many computer viruses, even recent ones, have been distributed through document macros in Microsoft documents. The same is easily doable with OpenOffice document macros.
I recommend the highest level of macro security together with a small list of trusted directories. The list should not include any directory where other software (browsers/mail apps) stores downloads. You could simply declare your personal folder for documents as a trusted folder if this is a folder where you put useful stuff and keep out questionable stuff. When you move/store any document to that place, it will be allowed to load itself with the embedded macro code.

Having a downloaded macro document loaded with a message that the code won't be executed due to security settings, think twice, read the embedded macro code, call menu:File>SaveAs and save the document in your personal trusted documents folder or any of its subfolders, then call menu:File>Reload and the macros will work (if they work) without any warning message.

Why I avoid the Standard library for the useful stuff:
1) Under some circumstances which are unknown to me, the office installer replaces your existing Standard library with a new one deleting all modules in that library. Happened to me twice in 15 years.
2) You can not build any packages from the standard libraries, no matter if it is global lib "Standard" in "My Macros" or "Standard" embedded in some document. Userdefined libraries with descriptive names let you create packages to backup and distribute code. The libraries tab of the macro organizer allows you to import non-standard libs from documents, macro folders and from packages.
Modules with descriptive names can be copied to other libraries by means of Ctrl+Drag&Drop in the modules tab of the macro organizer. This does not work when "Module1" exists in the target lib.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
BubikolRamios
Posts: 91
Joined: Sat Jan 04, 2014 1:28 pm

Re: [Solved] Macros lost

Post by BubikolRamios »

Note, another reason macro is gone is that you accidentally put something in front of sub & save , like:


foosub something
,,,,,
end sub


Luckily all is still there
OPen office 4.1.5/ win 7
Post Reply