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.