LibreOffice Calc copy range from one workbook to another

[camarillamike]

I am currently working on converting an Excel document into a LibreOffice document, I’ve actually got the majority of the macro’s involved transferred and converted them to use Star correctly, however I’m running into an issue with setting values/strings between workbooks and am having trouble locating what the exact issue is, I realize it’s likely something simple that I’m just missing but I’m hoping I can get a little bit of help.

Excel Macro Snippet:
Sub Transfer_Sheet()

Dim wbc

filetoopen = Application.GetOpenFilename _
(Title:=“Please Choose the Previous Character Sheet File”)

If filetoopen = False Then

MsgBox “No file specified.”, vbExclamation, “Please Choose File.” ’ Notification that nothing is chosen

Exit Sub

Else ’ Load the file, copy the first sheet and paste it in active sheet …

Set wbc = Workbooks.Open(filetoopen)

Dim pswdwb As String

pswdwb = Sheets(“Passwords”).Range(“wbpass”)

ThisWorkbook.Unprotect pswdwb

ThisWorkbook.Activate

ThisWorkbook.Sheets(“CharacterSheet”).Range(“D4:G6”).Value = wbc.Sheets(“CharacterSheet”).Range(“D4:G6”).Value

LibreOffice Macro Snippet so far:

Sub Transfer_Sheet()

Dim FilePicker As Object

Dim FilePath() As String

Dim wbc

Dim lurl as String

Dim CharSheet as object

oCharSheet = ThisComponent.Sheets.getByName(“CharacterSheet”)

FilePicker=createUnoService(“com.sun.star.ui.dialogs.FilePicker”)

FilePicker.execute

FilePath()=FilePicker.GetFiles

dispdir=FilePicker.DisplayDirectory

lurl=ConvertToURL(filepath(0))

If file = “” Then

MsgBox “No file specified. Please Choose File.”, ’ Notification that nothing is chosen

Exit Sub

Else ’ Load the file, copy the first sheet and paste it in active sheet …

wbc = starDeskTop.loadComponentFromUrl (lurl, “_hidden”, 0, Array())

Dim pswdwb As String

pswdwb = ThisComponent.Sheets.getByName(“Passwords”).getCellRangebyname(“A1”).String

ThisComponent.Unprotect pswdwb

oCharSheet.getCellRangebyName(“D4:G6”).SetString(wbc.Sheets.getByName(“CharacterSheet”).getCellRangebyName(“D4:G6”).String)

The LibreOffice Macro works up until the point where I try to copy the text from the previous file range into the new one, if I narrow it down from a range of D4:G6 to just a single cell then it work fine, but I really need to be able to select very specific ranges to transfer.

These ranges are ranges where user’s insert data the other cells on the sheet have formula’s populated in them that cannot be overwritten/changed and provide information based on user input.

Thank you in advance for any guidance you can give.

[arindam]

To get a range use this:

    	my_range = ThisComponent.Sheets(0).getCellRangebyName("A1:B1")
    	arr = my_range.getDataArray()

To set the value to another range:

    	my_range = ThisComponent.Sheets(0).getCellRangebyName("A3:B3")
    	my_range.setDataArray(arr)

Remember - the range operation should be performed in a same size of array only. In above example, A1 cell value stored in arr(0,0) and B1 is stored in arr(0,1).

Visit: http://www.debugpoint.com/category/libreoffice for more macro tutorials.