[Solved] Copying cell values from remote files using macro

[zwora]

Hi,

I've got file A in which by a macro I open another file (B) to get some data from there. It works fine until I try to copy cells with formulae inside. I would like to copy the value of the formula instead of formula. To open file B I use loadComponentFromURL() function.

Thank you

[Zizi64]

The normal Copy/Paste functions will copy all of the cell contents (value, string and formula) and the formatting properties.

use the paste special function with the macro recorder, or write your macro based on the API functions. You can get the Value only from the source cell with the API functions, and you can put it into the target cell.
API: Application Programming Interface.

[zwora]

Ok. I am using OO Basic and copy using the following code:

Code: Select all

oDoc1.CurrentController.Select(oRange1)
oDispatcher.executeDispatch(oFrame1, ".uno:Copy", "", 0, aOpenProperties())
oDoc2.CurrentController.Select(oRange2)
oFrame2 = oDoc2.CurrentController.Frame
oDispatcher.executeDispatch(oFrame2, ".uno:InsertContents", "", 0, aOpenProperties())

What should I change in my code to get value instead of the formula? Maybe there is some evaluating function or cast-like operator?

[zwora]

I was trying with uno:PasteSpecial and it was almost ok (just the Paste Special dialog appears), but the solution is much easier. I just did it as i was copying ranges in the same document and that works:

oRange1.setDataArray(oRange2.getDataArray)

where oRange1is in document A nad oRange2 is in document B.

But it works when I copy from B to A. In opposite direction (A to B) I need to use oDispatcher.executeDispatch()

[Zizi64]

But it works when I copy from B to A. In opposite direction (A to B) I need to use oDispatcher.executeDispatch()

Please upload your macro code and two ODF type sample file here.

[zwora]

The solution I used works fine for me, so my problem is solved. I should to put [Solved] in the topic of first post, but the topic content is too long to add anything there. Now I am chasing through other problems (described in other threads) however I consider OO Calc as the amazingly powerful tool with almost unconfined possibilities. The only problem is to know, hot to properly use it.