Ask Your Question
0

LibreOffice Calc copy range from one workbook to another. [closed]

asked 2014-05-21 18:57:48 +0100

camarillamike gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 11:45:22.181714

1 Answer

Sort by » oldest newest most voted
2

answered 2014-09-28 17:59:54 +0100

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/li... for more macro tutorials.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-05-21 18:57:48 +0100

Seen: 4,710 times

Last updated: Sep 28 '14