Running macro from another file's macro

[zwora]

Hi,

I need to do something like this: I've got one spreadsheet open and using macro I open another spreadsheet and send there some data (that I've already done using loadComponentFromURL() function). But now I need to run the macro from that second (newly open) file. And that gives me a trouble. It could be also running the button, as the macro I would like to run is connected to one.

Any help appreciated

[FJCC]

Please don't ask two questions in a single thread. It is likely to make the discussion difficult to follow. Please start a second thread for your question about copying values instead of formulas.

If you need to call the same code from two different documents, store the code in the My Macros container which should be at the top of the list of possible storage sites when you go to save the macro. The code will then be available to all documents. If you need to do something other than that, please explain it in detail.

[zwora]

Ok. Sorry. I deleted other question and will start a new post.

Regarding this post: I would lie to treat the files separately. Let's say I run file A. It opens (using macro) a file B and transfers there some data. Then I run macro in file B which calculates something based on transfered data and sends a result to file A. I don't want to make macros accesible to multiple files. I just need to trigger the macro in file B using file A (or remotely press button in file B assigned to a macro I would like to run).

[FJCC]

If there is no manual intervention between the running of the first and second macros, then all the code can be in document A. The concept is something like this

Code: Select all

'GET CELL RANGE IN DOC A
oCellRng = ThisComponent.Sheets.getByIndex(0).getCellrangeByName("A1:B100")

'GET DOC B
oDocB = loadComponentFromURL(DocB_URL, "_blank",0,Array())

'CODE TO TRANSFER DATA FROM oCellRng INTO oDocB AND PROCESS IT
....

'GET CELL RANGE IN DOC B TO TRANSFER TO DOC A
oCellRngB = oDocB.Sheets.getByIndex(0).getCellrangeByName("D1:E100")

'CODE TO TRANSFER DATA FROM oCellRngB to DOC A
...

Will that work or is there something I am not understanding?

[zwora]

But I kow how to transfer data in cells to another file. That is not my question. I am asking how to run macro assigned to file be from the macro in file A. I don't want to write new macro in A to operate on cells from B. I would like to trigger macro that is already in B from file A. So I need to get access to macros in file B while I am using file A.

[FJCC]

I don't know of any way to execute a macro in another file. Perhaps someone else will have a suggestion. There may be a way using the ScriptProvider service. You might also be able to hack a solution by tying the macro in document B to one of the events listed under Tools -> Customize -> Events. Storing all of the code in one place seems like a cleaner solution, but it is up to you.

[zwora]

The idea with triggering events might work. I will check that tomorrow. I definately need to run macro in B from A to avoid having big A file. In fact I will run macros in many different files A, which causes the code to be cleaner. It is like dividing job into smaller pieces, that can be used many times by different files.

[zwora]

I defined a trigger in the following way:

Code: Select all

Sub AddListener
	Dim oDoc, oSheet, oCell as Object
	oDoc = ThisComponent
	oSheet = oDoc.Sheets.getByName("sheet1")
	oCell = oSheet.getCellRangeByName("BG5")
	oListener = createUnoListener("Modify_","com.sun.star.util.XModifyListener")
	oCell.addModifyListener(oListener)
End Sub

Sub Modify_modified(oEv)
	ExportNewPDF
End Sub

It works well when I change cell BG5 from current document, but does not if I open document form another one. I tried to run AddListener sub on many different events (i.e. On Open Document, On Activate Document, On Save Document) but it does not work. It seems that opening form another document is protected from running macros in the document opened.