SERIOUS MACRO Bug(Entire Modules Disappearing w/ CTRL-Z)

[Vlad781]

Hey all,

So after over 18 hours spent learning and figuring out different commands and codes needed to 'parse' a sql file (really just a csv), and getting it formatted just right into my calc speadsheet, the whole module I was working on, disappeared. I have no idea where it went, it was also linked to a button click event. Which, when clicked, would run the script I was working on, but now it cannot find the macro file, an error box is given. and when going to the Tools->Macro->Organize Macro->OpenOffice Basic, its not where it was before.

This is exactly what happened:
I was writing/running this said file. Was pleased with what I had finally finished. Wanted to add some extra formatting to some cells, so to figure out how to bold and center align:
I made Calc the active window and wanted to record a new macro to see what the script was for achieving aligned and bolded text. I started macro, and made some changed to some cells.
clicked to stop macro. saved with a new name under same module as original file, so both macros are on the same file...
went back to the window with the macro file holding both macros. I hit CTRL-Z for some reason hoping to undo what I had written myself before recording the new macro. and BAM. the whole file becomes blank. All text/script code is gone.. I cannot press CTRL-Z or CTRL-Y to recover what just happened.

Does anyone know why this happens, or how to retrieve all the effort I put it? I am extremely upset that such a seemingly irreversible thing could happen.

Thanks all.

[Villeroy]

When you save Basic modules in a document and then store the document as plain text, the module is lost (of course).

[Vlad781]

When you save Basic modules in a document and then store the document as plain text, the module is lost (of course).

Sorry, could you please elaborate? I don't feel as though I made any mistake..

[Villeroy]

Csv files contain nothing but raw data in plain text.
When you save an office document as plain text, everything else will be lost.
Csv is a database exchange format in plain text. Therefore it is best used with database programs or with plain text editors. http://csved.sjfrancke.nl/ is a plain text editor for csv. Spreadsheets are inadequate, particularly when you are not familiar with spreadsheets.
If you need csv data to feed your spreadsheet calculation models, you better use Base together with Calc templates. Macros are a pestilence, particularly the badly written ones.
https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=23727

[jrkrideau]

When you save Basic modules in a document and then store the document as plain text, the module is lost (of course).

Sorry, could you please elaborate? I don't feel as though I made any mistake..

It sounds like you saved the file in .csv format. As Villeroy says, a .csv file saves nothing but the actual data. A .csv file and a .txt (text) file are the same thing for all intents and purposes here.

So you just told AOO to throw away all the formatting, macros, etc., and just save the data. The immediate solution is to do a Save As and save the file as a .ods file.

In the longer term Villeroy is correct. You should really be doing this in BASE and then pulling in the data to Calc as needed. Perhaps a bit more complicated but cleaner and easier in the long run. And you already have the SQL figured out for BASE

[Vlad781]

Sorry, maybe I wasn't clear.

I have a .sql file that is in csv format that is on my harddrive it is linked to my calc sheet using the Sheet From File dialogue under Insert. This file is set to be 'linked'.
Upon my button's click:
My macro downloads an updated version of this .sql from the internet by running a .sp1 powershell script,
then the macro updates the new updated file into my calc document, which is saved as a .ods file.
the macro formats and removes unnecessary chars and strings and empty columns.

Thats how things have been running... But after recording a new macro into the same module, the previous macro (Above) completely disappeared.

[Villeroy]

The "sql files" I use to know are plain text files with INSERT statements to insert new records into a database and that is what you should do for simplicity and for data integrity. Converting the SQL commands to plain text (csv) in order to use the data in an arithmetic calculator (Calc) is weird. Writing macros can be extremely counter productive as you have seen. Without seeing your .sql nor your .csv it is impossible to give any further advice.

[Vlad781]

Well the problem isn't in my sql or calc file... And now I do realize that the sql file does have INSERT commands. My counterproductivity is now evident. But thats not the main point. The problem is that the entire macro file simply became a blank file after I recorded and and pressed CTRL-Z . It shouldn't have disappeared!

[B Marcelly]

Hi,

clicked to stop macro. saved with a new name under same module as original file, so both macros are on the same file...
went back to the window with the macro file holding both macros. I hit CTRL-Z for some reason hoping to undo what I had written myself before recording the new macro. and BAM. the whole file becomes blank. All text/script code is gone.. I cannot press CTRL-Z or CTRL-Y to recover what just happened.

I have found a way to reproduce:

1. Open a new Calc document.
2. Record a macro, click button Save. Choose to save in the document.
3. Open the IDE : you have a macro Main in library Standard, Module1. Keep the IDE open.
4. Record a second macro, click button Save. Message : "Do you want to overwrite the 'Main' macro ?"
   Answer No, but keep the save window open.
5. In the IDE, rename macro Main into Main1. Keep the IDE open.
6. Click again button Save
7. Now the IDE shows macro Main1 followed by the new macro Main. Perfect !
8. Click Ctrl-Z : module Module1 is now entirely blank.
9. Menu Edit > Redo gives back both macros.

I think you did something like this.

Let us look at another manipulation:

1. Open a new Calc document.
2. After you have recorded your first macro, rename it in the IDE.
3. Close the IDE.
4. In the macro selector, select only the library Standard, not the module. Close the macro selector.
5. Record another macro. At save, keep selection on Standard library : a macro Macro1 is added to your renamed macro, in Module1.
6. Repeat steps 3 and 4 : a Macro2 is added to the existing macros in Module1.

The macro recorder is designed for quick and dirty work by newbies. It seems the designer did not suppose that such user would create different modules or edit macros the way you did it.

In the rare cases when I record macros, I make sure that it deletes the Main macro of a newly created module. Then I move the macro where I want.