Copy cell range from File1 sheet1 to a cell File2 sheet1

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
valimaties
Posts: 9
Joined: Wed Oct 31, 2018 1:36 pm

Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by valimaties »

Hi all.

First I wanna say is my first post on this forum. I use OppenOffice Calc v 4.1.5 . I'm a Visual FoxPro 9 programmer, and what I try to do now is to copy content from an unformatted xls file to an ods spreedsheet by opening these two files in VFP.
Till now, I've made a class in VFP which open an ods file and insert some data from vfp cursor, cell by cell. But I don't know how to write the code to copy content from a file to another file. I have read some exemple of code and I tried them, but nothing works, because, I think, there was to copy content from a sheet to another sheet in the same document/file, not two different documents/files.
I need to use getCellRangeByPosition() function and getCellByPosition() function, because range of data to copy is not fixed, it is a generic range of data, depending on my cursor from VFP.
For exemple, I have to copy from File 1 (xls) spreedsheet 1 (row 1, column 1,row 5450 , column 12 ) to File 2 (ods) spreedsheet 1 (row 5, column 1).

Thanks

Regards,
Vali
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
valimaties
Posts: 9
Joined: Wed Oct 31, 2018 1:36 pm

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by valimaties »


Thanks Villeroy, but it does not work as I intent to do. Because in this example the copy environment is happening in destination file. I have to use another programming language as "envelope" and to copy data from an xls file to an ods file.

So, I have attached 2 files, as I use as example files.

a.ods is the file I use as final file, which has only headers for columns.
Excel.xls is the file with data which has to be copied to a.ods as records for those headers.

Sorry for my english...
Attachments
a.ods
(7.92 KiB) Downloaded 180 times
excel.xls
(6 KiB) Downloaded 189 times
OpenOffice 4.1.5 on Windows 7 x64
User avatar
valimaties
Posts: 9
Joined: Wed Oct 31, 2018 1:36 pm

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by valimaties »

I think I will do in other way. I will use the excel file as primary file, because contains all data I need, and I will insert the headers texts for columns. After that I will format the cells for columns and that's it, is faster.

PS: I need this quickly, so I found an alternate solution. My problem was the time to insert a lot of data in a file. So I will export my VFP cursor to xls, which is an instant process, and I will use excel file to add headers and formatting columns of data.
PS: in any way, a solution to post title is good to know

Thanks.
OpenOffice 4.1.5 on Windows 7 x64
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Lupp »

First I have to admit:
-1- I didn't find the time yet to thoroughly study the suggestions by Villeroy.
-2- I don't feel sure I understood correctly everything the OP explained.

However, returning to the original question I suppose the example below using the slot machine for the purpose might be helpful. The slot machine delegated the task to the StarDesktop basically, I think. Of course, the target document needn't be new. (I just found it the simplest way to test the routine.)

Code: Select all

Sub copySelectedRangeFromSheetToNewDocDemo()
dh       = CreateUnoService("com.sun.star.frame.DispatchHelper")
docSrc   = ThisComponent
frSrc    = docSrc.CurrentController.Frame
sheetSrc = docSrc.Sheets(0)
rgSrc    = sheetSrc.GetCellRangeByName("A1:C10")
docSrc.CurrentController.Select(rgSrc)
dh.ExecuteDispatch(frSrc, ".uno:Copy", "", 0, Array())
docTrg   = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, Array())
frTrg    = docTrg.CurrentController.Frame
sheetTrg = docTrg.Sheets(0)
cellTrg  = sheetTrg.GetCellRangeByName("G11")
frTrg.Activate()
docTrg.CurrentController.Select(cellTrg)
dh.ExecuteDispatch(frTrg, ".uno:Paste", "", 0, Array())
End Sub
I abstained from creating object variables for the CurrentController_s because I once got unresolved issues this way in LibreOffice.
The demo as it ran in AOO V4.1.5 under Win 10:
Attachments
aoo95710CopyPateAccrossSpreadsheetDocuments_1.ods
(10.96 KiB) Downloaded 184 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Villeroy »

He/she wants to preserve the formatting of the target sheet. You transfer data into preformatted cells when you insert cells before pasting or by using a database connection between the two sheets.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Lupp »

Well then:

Code: Select all

Sub copySelectedRangeFromSheetToTargetLocationInOtherDocDemo()
dh       = CreateUnoService("com.sun.star.frame.DispatchHelper")
docSrc   = ThisComponent REM May be replaced with a first StarDesktop.LoadComponentFromUrl() call.
frSrc    = docSrc.CurrentController.Frame
sheetSrc = docSrc.Sheets.GetByName("Sheet1")
rgSrc    = sheetSrc.GetCellRangeByName("A2:C3")
docSrc.CurrentController.Select(rgSrc)
dh.ExecuteDispatch(frSrc, ".uno:Copy", "", 0, Array())
docTrg   = StarDesktop.LoadComponentFromUrl(ConvertToUrl _
  ("CompletePathnameOfOtherSpreadsheetDoc.ods"), "_blank", 0, Array())
frTrg    = docTrg.CurrentController.Frame
sheetTrg = docTrg.Sheets.GetByName("Sheet1")
cellTrg  = sheetTrg.GetCellRangeByName("A2")
frTrg.Activate()
docTrg.CurrentController.Select(cellTrg)
Dim PasteSpecialArgs(5) As New com.sun.star.beans.PropertyValue
PasteSpecialArgs(0).Name = "Flags"
PasteSpecialArgs(0).Value = "SVD"  REM "StringValueDate"
PasteSpecialArgs(1).Name = "FormulaCommand"
PasteSpecialArgs(1).Value = 0
PasteSpecialArgs(2).Name = "SkipEmptyCells"
PasteSpecialArgs(2).Value = false
PasteSpecialArgs(3).Name = "Transpose"
PasteSpecialArgs(3).Value = false
PasteSpecialArgs(4).Name = "AsLink"
PasteSpecialArgs(4).Value = false
PasteSpecialArgs(5).Name = "MoveMode"
PasteSpecialArgs(5).Value = 4
dh.ExecuteDispatch(frTrg, ".uno:InsertContents", "", 0, PasteSpecialArgs)
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
valimaties
Posts: 9
Joined: Wed Oct 31, 2018 1:36 pm

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by valimaties »

Thank you Villeroy and Lupp for your responses.

I found a post here on this forum how to instantiate and use DispatchHelper.
(Here viewtopic.php?f=20&t=83813)
I used till now only Desktop frame, which means I had to convert somehow the macros made in calc :) Creating DispatchHelper object in VFP it will be easy, because I can easily convert macros...

Thank you... I will come back with results of your code, Lupp.

Regards,
Vali
OpenOffice 4.1.5 on Windows 7 x64
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Lupp »

To create the DispatchHelper object requires one line of Basic code.

To use it is a very different thing because
-1- the so called slots of the machine in charge of executing the "uno:" commands are rather poorly documented.
-2- you may need to do research based on recorded macros to get informed about supported arguments ...
-3- but sometimes the recorder creates dysfunctional code (I had such cases with using filters in export). You need a lot of educated guessing then. The cases I saw in LibO tried to avoid a large properties array with couples of names and values defined in pairs of lines. This failed.
-4- you may be (feel) mislead by specifics of slot parameters as compared with API parameters and the predefined constants. In my above example you find MoveMode=4 e.g. meaning NOTHING to move despite the fact that this is the default (should have value 0 imo), and the functionally comparable constant com.sun.star.frame.CellInsertMode.NONE has the numeric value 0 indeed. Setting MoveMode=0 for the mentioned slot would reult in moving cells below DOWN which is value 1 for CellInsertMode. (Did you know that software is a mess?)

In the code I posted in my previous answer to Villeroy I had to do just a tiny little bit of that kind of research, however.

(As I mostly prefer LibreOffice my primary source concerning the slot machine is
https://wiki.documentfoundation.org/Dev ... chCommands. The list will surely contaion slots not available in AOO.
The slightly(?) outdated(?) https://wiki.openoffice.org/wiki/Framew ... x_Commands lists the command for OO.o.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
valimaties
Posts: 9
Joined: Wed Oct 31, 2018 1:36 pm

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by valimaties »

Yes, indeed, it takes one line of code in VFP, too (plus one :D ) . I have to instantiate first ServiceManager object, and after that create an instance of DispatchHelper. This I've saw is simple, now. But my question now comes with the fifth parameter I have to send to executeDispatch() command, which I have saw that it is Array() . What this Array() represents?
OpenOffice 4.1.5 on Windows 7 x64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Villeroy »

http://www.openoffice.org/api/docs/comm ... teDispatch
This API does not support any named arguments nor optional arguments. You always need to pass something. In the Basic language the function call Array() returns a blank array.
The 5th argument of execute Dispatch takes an array of named arguments as com.sun.star.beans.PropertyValues. These property values are the way to use something like named arguments as demonstrated by Lupp. Valid named arguments for dispatch calls are poorly documented.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
valimaties
Posts: 9
Joined: Wed Oct 31, 2018 1:36 pm

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by valimaties »

It worked sending the same array with com.sun.star.beans.PropertyValues, with Name="ToPoint" and Values="<CellToCell>" :) Also, worked sending empty array.
OpenOffice 4.1.5 on Windows 7 x64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cell range from File1 sheet1 to a cell File2 sheet1

Post by Villeroy »

Dispatch macros work with the current selection. They just mimic actions in the user interface. If no "ToPoint" is given, I would expect that the current selection is used. All this is not well documented.
What is wrong with my code? It expands a preformatted range before dumping arrays of raw data into the expanded cell range.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply