Copy data from other calc workbooks

[dangermaus]

I am trying to create a macro that copies a cell range from 3 different workbooks and paste them into one workbook on different sheets. I want the macro to be in the destination workbook where the data will be pasted. All 4 workbooks will already be open.
I have tried to use the code from this post below but I can’t get it to work for me. Probably because I am not great at this.
link

I have looked through the LibreOffice BASIC Programming Guide but have yet to find a way to reference another workbook.
Can anyone point me in the right direction?

[schiavinatto]

I find it easier to use linked data.
1 - in the source file, name the area to be copied. and save file
2 - in the grouped file, in Spreadsheet Menu / External Links,
3 - select the file, from Enter.
4 - Select the area named above
5 - If you want to automatically update, adjust the value.

---

Creio ser mais fácil usar dados vinculados.
1 - no arquivo fonte, nomear area a copiar. e salvar arquivo
2 - no arquivo agrupado, em Menu Planilha / Links externo,
3 - selecione o arquivo, de Enter.
4 - Selecione a area nomeada acima
5 - Se quiser atualização automatica ajustar valor.

[dangermaus]

Thanks for the suggestion but I need to copy the data and not link it.

[dangermaus]

I managed to get something working but I wonder if it needs to be cleaned up. Any thoughts on my code below?

sub copy
rem ----------------------------------------------------------------------
rem define variables
GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
dim document   as object
dim dispatcher as object
dim oDocA As Object, oDocB As Object, oSheetA As Object, Dummy(), oRangeA as object, targetcell as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
Set oDocA = CreateScriptService("Document", "book2.ods")
oDocA.Activate()
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDocA = ThisComponent
oFrameA = oDocA.CurrentController.Frame
oSheetA = oDocA.getSheets.getByIndex(0)
oRangeA = oSheetA.getCellRangeByName("A1:B2")
oDocA.CurrentController.Select(oRangeA)
oDispatcher.executeDispatch(oFrameA, ".uno:Copy", "", 0, Dummy())

rem ----------------------------------------------------------------------
Set oDocB = CreateScriptService("Document", "book1.ods")
oDocB.Activate()
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$4"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub

[joshua4]

Since you already have the hard part done…getting at multiple workbooks (spreadsheets), might I suggest that once you have the ranges you use the range methods getDataArray() and setDataArray() instead of Dispatcher and select/cut/paste? This won’t copy formatting, but it would be neater to use the API, given that you asked about cleaning up. See How can I copy cell ranges between documents in a macro? - #2 by jimk.

[dangermaus]

Thanks. I will take a look.

[dangermaus]

So I noticed that my macro was stored under ‘My Macros & Dialogs’. I moved it to a module stored in the workbook where I run the macro from. When I run it will activate the second workbook but it copies the data from the workbook with the macro instead of the second workbook. If I run the exact same macro from ‘My Macros & Dialogs’ it works great.
Ultimately I don’t care where the macro is stored but I do need to be able to copy the workbooks and the macro to other computers. I assumed having it stored in the workbook was the best option for that.
Any ideas on why the macro isn’t working when I move it to the workbook?

[Wanderer]

@dangermaus

it copies the data from the workbook with the macro instead of the second workbook

Set oDocA = CreateScriptService("Document", "book2.ods")
oDocA.Activate()
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDocA = ThisComponent

I’d think the last line can explain this behaviour. If you bind a macro to a button in a document, it is defined, what ThisComponent will be. If you call from Menu > RunMacro ThisComponent can point to other documents also.

[dangermaus]

I run the macro from Menu > RunMacro so it sounds like that is the way to do it if I understand you correctly. The macro is able to copy from the second workbook when the macro is stored in ‘My Macros & Dialogs’ but doesn’t do that when I store the macro in a library inside the first workbook.