# why does calc macro run in writer? [closed]

I have a simple Calc macro that writes to a cell on save, and it works well. However, it is also trying to run whenever I edit a document in Writer. What do I need to do to limit this macro to running only in Calc?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2018-11-03 19:46:38.052418

Please reedit your Q here and attach the macro code that you cite.

( 2018-11-03 00:49:29 +0200 )edit

Sort by » oldest newest most voted

Hello,

This is happening probably because of the manner in which the macro was set to execute. The macro was likely placed in My Macros->Standard->Some Module. This macro was then tied to an event such as Save Document. It was also likely marked as Save in: LibreOffice. With this saved in LibreOffice, any module within LO will execute this macro (Writer, Calc, Base, etc.). The other choice is to save in the document itself but then only that document would have the macro execute.

If you want this macro to execute only for this particular document, delete the event from LibreOffice and add it back in Save in: XXXX.ods. Now only this document will have the macro executed.

If, however, you want this to work across ALL your Calc documents, leave the event setting as is but add a line of code at the beginning of your macro:

If NOT ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub


The will bypass the code for any module other than Calc.

more

Thanks very much... your assumptions are spot on, and your explanation makes sense.

I've been able to remove the macro from My Macros->Standard->Module1, and create it at XXXX.ods->Standard->Modules->Sheet1. I've also removed the old macro from the "Save Document" event in Customize, and assigned the new macro to that event. However, that doesn't seem to work at all... the macro doesn't update cell A1 in Sheet 1 or any other sheet. It should update A1 when any sheet is saved. What did I miss?

( 2018-11-02 20:34:39 +0200 )edit

OK. Again you have placed me at a disadvantage as this time I must guess at the code you have. Whether the macro is in My Macros or in the document should not be a factor. In My Macros it is available across LO & in the document it can only be used by the document. Just because it may be in My Macros doesn't mean other WILL execute it; only that they can. More .....

( 2018-11-02 21:12:34 +0200 )edit

This macro:

Sub SetTimestamp
If NOT ThisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then Exit Sub
oSheet = ThisComponent.Sheets.getByName("Sheet1")
myRange = oSheet.GetCellRangeByName("A1")
myRange.setValue(NOW())
End Sub


was tested and works in either My Macros or from within the document. It places the current date/time in Sheet1 cell A1 regardless of the sheet you may be working on when the save is done.

( 2018-11-02 21:17:58 +0200 )edit

Ah yes, including my macro would have been good... sorry. Here it is:

   Sub SaveDateTime
Dim my_sheet as object
Dim my_cell as object

my_sheet=thiscomponent.getcurrentcontroller.activesheet
my_cell = my_sheet.getCellbyPosition(0,0)

my_cell.String = Now
End Sub


I saved it in XXXX.ods->Standard->Modules->All. "All" is a module I created, of course.

When I originally added it to the document, it defaulted to the Sheet1 module, which also failed.

( 2018-11-03 01:23:53 +0200 )edit

I can of course use the macro you provided -- and thanks for that -- but I'd really like to understand why the one I have isn't working. I might actually learn something! :-)

( 2018-11-03 01:28:17 +0200 )edit

The macro itself works as is if what you want is the current date/time in cell A1 of the current sheet.

What is confusing is where you placed the Macro.

So it is placed in the document - that is XXXX.ods

In the Library name Standard

Now the confusion. In a Library you have modules where you place the code. Your comment seems to state a module named Modules and another module within that named All. Can't have modules within modules!

( 2018-11-03 01:51:43 +0200 )edit

I understand what you're saying, but what I see in Calc doesn't line up with what you said about modules.

At the top level of the Basic Modules window was "My Macros", "LibreOffice Macros", and "XXXX.ods". If I clicked on the + next to "XXXX.ods", I saw "Standard", "Forms", and "Modules". Clicking on the + next to "Modules" revealed "Sheet1", which I didn't create - it was already there.

I then created "All" under "Modules", and stored my macro there. Isn't that correct place to put a macro?

( 2018-11-03 21:32:11 +0200 )edit

Sounds right but your description is incorrect:

XXXX.ods   This is the document
Standard    This is the Library
Forms         Another Library
Modules       Another Library
Sheet1      A module in a Library -  click on this one shows macros in module


So it appears you created a new module under Modules named All. So where you stored the macro war really in: XXXX.ods->Modules->All. So I just set my sheet that way & have no problems running your code.

( 2018-11-03 22:01:04 +0200 )edit

First of all, thanks for the explanation of the hierarchy. I understood that "Sheet1" and "All" were modules, but didn't know that the items shown as "Standard", "Forms", and "Modules" were referred to as Libraries.

So I did have my macro in the right place, but for some reason it wasn't working. I'll keep examining things on my end because it has to be something I'm missing.

Thanks again for all the assistance. I have indeed learned a lot through this dialog.

( 2018-11-04 00:03:34 +0200 )edit