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