Using Macro VBA on LibreOffice

[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

[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.

[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

[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.

[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

[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

[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

[bocah#0257]

Ok, Big Thanks mate. I'll try it on my data