Page 1 of 1
Using Macro VBA on LibreOffice
Posted: Wed Feb 23, 2022 6:00 am
by bocah#0257
Hi Guys, can you solve my problem.
I try to copy paste a data from one file to another using a macro on LibreOffice.
I want to copy file FM to DAILY MONITORING perdate percage,
In FM file, i want to copy from row F to S
and copy from A1 in FM file to A1 in DAILY MONITORING file (copy to the first empty row in F in DAILY MONITORING
hope you understand.
I've been attach the example.
NB. You can change the file format.
Attached Files Attached Files
Re: Using Macro VBA on LibreOffice
Posted: Wed Feb 23, 2022 8:19 am
by Zizi64
Where is the VBA macro code? I can not find in your attached files. Do you want to convert an existing macro, does not?
The VBA (Visual basic for the Applications) is a Micro$oft related product. It is a programming language and environmet and lots of application-specific function in one. The Apache OpenOffice nor LibreOffice not contain such product. Some simple VBA macros can run in LibreOffice with the Compatibility option, but it not work with all of VBA codes.
The AOO and LO has an API. API: Application Programming Interface. You can call the thousands functions of the API from many supported programming languages.
If you mean that you want to create VBA macros for your .xlsx files, then here are my suggestions for this task:
- buy M$ Excel
or:
- Use the native, international Standard ODF file formats (Instead of the foreign file formats). (The .ods or .ots for the spreadsheets.)
- Use the StarBasic (or any other supported programming language) of the LibreOffice) for the macros (instead of the VBA).
- Study and use the API functions and procedures of the LibreOffice (instead of the VBA).
- Download, install, study and use one of these object inspection tools: MRI or the XrayTool.
Re: Using Macro VBA on LibreOffice
Posted: Wed Feb 23, 2022 8:37 am
by bocah#0257
Code: Select all
Sub Feedmaster01()
'
' Feedmaster01 Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Windows("FM#.xls").Activate
Range("F6:S6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY MONITORING.xls").Activate
Sheets("A1").Select
Range("F4").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("FM#.xls").Activate
Range("F7:S7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY MONITORING.xls").Activate
Sheets("A2").Select
Range("F4").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("FM#.xls").Activate
Range("F8:S8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY MONITORING.xls").Activate
Sheets("A3").Select
Range("F4").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("FM#.xls").Activate
Range("F9:S9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY MONITORING.xls").Activate
Sheets("A4").Select
Range("F4").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End Sub
This is the example of my code in Excel
Re: Using Macro VBA on LibreOffice
Posted: Wed Feb 23, 2022 8:57 am
by Hagar Delest
As said by Zizi64, either use MS Office with your current macro or convert your macro and work with ODF (.ods) with AOO or LO.
Using AOO/LO macros with OOXML (.xlsx) is not possible.
Please add
[Solved] at the beginning of the title in your
first post (top of the topic) with the
*EDIT button if your issue has been fixed.
Re: Using Macro VBA on LibreOffice
Posted: Wed Feb 23, 2022 1:48 pm
by JeJe
Use the macro recorder feature to generate the code to copy and paste. This thread should help you with selecting the range and moving the right window to the front for the copy and paste to work.
viewtopic.php?f=20&t=103718
Re: Using Macro VBA on LibreOffice
Posted: Thu Feb 24, 2022 3:18 am
by bocah#0257
can you guys give some code example for my problem. you can change file format with the format that compatible with LibreOffice macro. Thank in advance
Re: Using Macro VBA on LibreOffice
Posted: Thu Feb 24, 2022 6:10 am
by FJCC
Here is one method. Notice I changed the files to ods format.
Code: Select all
Sub MainTransferMacro
'Find the open documents
oComp = StarDesktop.getComponents()
Enum = oComp.createEnumeration()
While Enum.hasMoreElements()
oCandidate = Enum.nextElement()
IF oCandidate.supportsService("com.sun.star.sheet.SpreadsheetDocument") Then
IF oCandidate.Title = "FM.ods" Then oFM = oCandidate
IF oCandidate.Title = "DAILY MONITORING.ods" Then oDAILY = oCandidate
End If
WEnd
'Get FM Data and transfer
oFM_Sheet = oFM.Sheets.getByName("PAKAN KEMATIAN")
aDataSource = oFM_Sheet.getCellRangeByName("F6:S6").getDataArray()
TransferData(aDataSource, oDAILY, "A1")
aDataSource = oFM_Sheet.getCellRangeByName("F7:S7").getDataArray()
TransferData(aDataSource, oDAILY, "A2")
End sub
Sub TransferData(SourceData, TargetFile, SheetName)
oSheet = TargetFile.Sheets.getByName(SheetName)
ColF = oSheet.getCellRangeByName("F4:F58")
Empties = ColF.queryEmptyCells()
FirstEmptyRange = Empties.getByIndex(0)
FirstEmptyRow = FirstEmptyRange.RangeAddress.StartRow
DataTargetRange = oSheet.getCellRangeByPosition(5, FirstEmptyRow, 18, FirstEmptyRow)
DataTargetRange.setDataArray(SourceData)
End Sub
Re: Using Macro VBA on LibreOffice
Posted: Fri Feb 25, 2022 4:48 am
by bocah#0257
Ok, Big Thanks mate. I'll try it on my data