Macro for Closing an OO Spreadsheet

[Map941]

Hi,

I am completely new to OO Basic Macros and I am moving an Excel workbook to OO. I have been able to record the macros in OO except for making one macro close the spreadsheet. This should be a simple operation, but I have not been able to find the information for this.

I would greatly appreciate if anybody would post the code I should add to my macro.

Thank you in advance.

Maurizio

[Map941]

I have added the following code to the macro:
___________
oDoc = ThisComponent

oDoc.close(true)
Stardesktop.terminate()

end sub
_____________

It closes OK, but then it gives me an error message for Open Office.Document Recovery. This will be confusing to the users. I tried to add:
On Error Goto ExitErr
and placing:
ExitErr:
before:
End Sub
But it produced the same message.

Is there any way to avoid the error message?
Thank you.
Maurizio

[FJCC]

I played with this a bit and if you remove the Stardesktop.terminate() statement the error doesn't occur. I don't think that statement is necessary. If the document executing the macro is the only one open, the removal of the terminate statement doesn't have any drawbacks as far as I can see. If other documents are open they will remain open. You might not want that, but I wonder about the wisdom of running a macro that closes other documents.

[Map941]

Thank you for your suggestion. I implemented it, and it was still giving me the same error.

Then I separated a closing file macro. By itself, with your suggestion, and it closed the file without any error.

The macro I had before, without the closing of the file, run also without any error.

Finally, I combined the two macro under a master macro, The file now close, but I get the same error message. In other words, only if I run the two programs independently from the Run Macro dialog I have no error. Something must remain open from the first macro... Is there a way to stop that?

Finally, if I make two buttons one for "Back up" and the other for "Close", I get the message "soffice.bin has encountered a problem and must be closed". This is better because it does not open Open Office again.

Thank you for any help you can give me. It is such a minor thing and I could ignore it, but the users I have may really get confused.
Maurizio

[FJCC]

Without knowing what your macro is doing, it can't guess at what the problem might be. Perhaps someone else has seen this kind of problem and can suggest a solution. If the macro isn't too huge, you might try posting it. It will be easier to read if you paste it into your post, select that range of text and press the Code button.

[Map941]

By playing with it I got a message saying that the error originated from trying to close the file while Basic was still opened.

This situation is similar to the post "Macro doesn't save&close Calc if I don't click". I tried to use some of the information there, but cannot close the file from the macro without generating an error.

My code is:

Code: Select all

Sub CloseMyDocNow

   oDoc = ThisComponent

   oDoc.close(true)
   Stardesktop.terminate()
   
End Sub

and

Code: Select all

sub MySaveAndBackUpWithSaveAs
rem - This save the file then saves it again with a new name and the name of the open file
rem - is the Back up
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document

rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem - Save Document with same name 
dispatcher.executeDispatch(document, ".uno:Save", "", 0, Array())

rem ----------------------------------------------------------------------
rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///C:/Documents%20and%20Settings/Owner/Desktop/Groups/BackUP_SGG_Record_Form.ods"
args1(1).Name = "FilterName"
args1(1).Value = "calc8"

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())

end sub

The problem I have with this is that the users do not understand much about computers. After saving and back up the file still open is the back up. If they do not close the file and continue to enter data, the data will be stored in the back up, but not in the file that will be opened next time. So it will be lost with the next back up.

[FJCC]

I tried running your code as two separate subs called from a Main routine and also merged it into a single macro. As before on my system, if I leave the Stardesktop.terminate() statement out, it runs without error. I don't know enough about macros to explain why you get an error when you remove the terminate statement. Your comment about Basic still being open leads me to ask if you get the error if you run the macro with the Basic IDE closed. I tried it with the IDE open on my system and there isn't a problem, but maybe your situation is different somehow.

[Map941]

Thank you FJCC for the time you spent helping me.

I reinstalled OO and updated from 3.0.0 to 3.0.1. Then, as you suggested, I deleted the Stardesktop.terminate() statement and I run a sub main calling the two sub. This is what I got:

If I run sub main form the dialog from the macro menu everything works fine.

However, if I run the macro with a button associated with the same sub main, I get the error message
"soffice.bin has encounterd a problem and needs to close...." This is not too bad as all the processes I wanted to run are completed: saving, back up and closing the program.

If you get the same problem, I think we have found a bug in the macro associating API. Let me know so that we can report it.

Thank you again.
Maurizio

[FJCC]

I don't get an error running the macro from a button. I have attached my version of your code. I placed a button on sheet1 to execute the macro. If it causes an error on your system, then we can look refocus on you installation of OOo. If it doesn't cause an error, perhaps you could post your file and I'll try it on my system.

[Map941]

Thank you for sending your file!!!!

You solved my problem. I found out what I was doing that was not working.

Your file forks fine for me too. The only difference was that I did not use a "control button" but a "rectangular graphic" and associated the macro with it. That can be done in Excel and evidently cannot be done in Calc with the same flexibility. I could run a macro from a rectangle but it was giving an error if the macro attempted to close the file.

Now I made the change and I no longer get the error message.

I am glad that I could figure out this because I am planning to develop other macros in OO. This was my first trial, and I needed to deliver the file asap.

Thank you again.
Maurizio