Copy cell range from File1 sheet1 to a cell File2 sheet1
- valimaties
- Posts: 9
- Joined: Wed Oct 31, 2018 1:36 pm
Copy cell range from File1 sheet1 to a cell File2 sheet1
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
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
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- valimaties
- Posts: 9
- Joined: Wed Oct 31, 2018 1:36 pm
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
Villeroy wrote:viewtopic.php?f=21&t=77069
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...
OpenOffice 4.1.5 on Windows 7 x64
- valimaties
- Posts: 9
- Joined: Wed Oct 31, 2018 1:36 pm
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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.
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
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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.)
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:
-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
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
---
Lupp from München
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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
---
Lupp from München
- valimaties
- Posts: 9
- Joined: Wed Oct 31, 2018 1:36 pm
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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
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
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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.)
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
---
Lupp from München
- valimaties
- Posts: 9
- Joined: Wed Oct 31, 2018 1:36 pm
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
Yes, indeed, it takes one line of code in VFP, too (plus one ) . 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
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- valimaties
- Posts: 9
- Joined: Wed Oct 31, 2018 1:36 pm
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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
Re: Copy cell range from File1 sheet1 to a cell File2 sheet1
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice