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

Discuss the spreadsheet application
Post Reply
Vlad781
Posts: 4
Joined: Sat Nov 28, 2015 10:57 am

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

Post by 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.
Last edited by Vlad781 on Sun Nov 29, 2015 5:51 am, edited 1 time in total.
OpenOffice 4.1.2 on Windows 10, lenovo yoga 3 pro
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SERIOUS MACRO ISSUE (Entire Modules Disappearing)

Post by Villeroy »

When you save Basic modules in a document and then store the document as plain text, the module is lost (of course).
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
Vlad781
Posts: 4
Joined: Sat Nov 28, 2015 10:57 am

Re: SERIOUS MACRO ISSUE (Entire Modules Disappearing)

Post by Vlad781 »

Villeroy wrote: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..
OpenOffice 4.1.2 on Windows 10, lenovo yoga 3 pro
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SERIOUS MACRO ISSUE (Entire Modules Disappearing)

Post by 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
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
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: SERIOUS MACRO ISSUE (Entire Modules Disappearing)

Post by jrkrideau »

Vlad781 wrote:
Villeroy wrote: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 :)
LibreOffice 7.3.7. 2; Ubuntu 22.04
Vlad781
Posts: 4
Joined: Sat Nov 28, 2015 10:57 am

Re: SERIOUS MACRO ISSUE (Entire Modules Disappearing)

Post by 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.
OpenOffice 4.1.2 on Windows 10, lenovo yoga 3 pro
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SERIOUS MACRO ISSUE (Entire Modules Disappearing)

Post by 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.
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
Vlad781
Posts: 4
Joined: Sat Nov 28, 2015 10:57 am

Re: SERIOUS MACRO BUG(Entire Modules Disappearing When CTRL-

Post by 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!
OpenOffice 4.1.2 on Windows 10, lenovo yoga 3 pro
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

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

Post by B Marcelly »

Hi,
Vlad781 wrote: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.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
Post Reply