Why does calc macro run in writer?

[fmk2]

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?

[EasyTrieve]

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

[Ratslinger]

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.

[fmk2]

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?

[Ratslinger]

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 …

[Ratslinger]

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.

[fmk2]

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.

[fmk2]

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!

[Ratslinger]

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!

[fmk2]

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?

[Ratslinger]

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.

[fmk2]

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.