Can some one help me with creating macro in Libre Office Version: 6.3.5.2

English
#1

Hi,

I have already created my macro in excel vba and works completely fine; however, I am unable to create it in Libreoffice on given version.

Here are the macros which works fine on ms excel vba:
1st one is to add details:

Sub Add()

Dim wb As Workbook

If Sheet3.Range("D6").Value = "" Then
    MsgBox "Please add Associate ID into Database"
Exit Sub
Else
    If Sheet3.Range("D8").Value = "" Then
        MsgBox "Please add Associate Name into Database"
    Exit Sub
    Else
        ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
        ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Copy
        Set wb = Workbooks.Open(Range("H1") & "Database.xlsm")
        wb.Worksheets("Employee Details").Activate
        lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Cells(lastrow1 + 1, 1).Select
        ActiveCell.PasteSpecial xlPasteValues
        ThisWorkbook.Activate
        ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
        Selection.Copy
        wb.Worksheets("Employee Details").Activate
        lastrow2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
        ActiveSheet.Cells(lastrow2 + 1, 2).Select
        ActiveCell.PasteSpecial xlPasteValues
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        
        Set wb = Nothing
        
        Application.CutCopyMode = False
        ThisWorkbook.Activate
        MsgBox "Associate Name - " & Cells(8, 4).Value & " & Associate ID - " & Cells(6, 4) & " Added Successfully"
        ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
        Selection.ClearContents
        ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
        Selection.ClearContents
    End If
End If

End Sub

2nd one is for allocation almost similar to add
Sub Allocate()

Dim wb As Workbook
Dim Key As Variant

If Range("G10") = " " Then
    MsgBox "Please add associate into Database"
Exit Sub
Else
    If Range("D8") = "" Then
        MsgBox "Please Input Key No."
    Else
        Key = Range("D8")
        Set wb = Workbooks.Open(ThisWorkbook.Worksheets("Employee Addition").Range("H1") & "Database.xlsm")
        wb.Worksheets("Transactions").Activate
        Range("B1").Select
        Set Rng = Cells.Find(What:=Key, _
        After:=ActiveCell, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False)
            If Rng Is Nothing Then
                'Copy & Paste Key No. To Another Workbook
                ThisWorkbook.Activate
                ThisWorkbook.Worksheets("Allocation").Cells(8, 4).Select
                Selection.Copy
                wb.Worksheets("Transactions").Activate
                lastrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                ActiveSheet.Cells(lastrow1 + 1, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                'Copy & Paste Associate ID To Another Workbook
                ThisWorkbook.Activate
                ThisWorkbook.Worksheets("Allocation").Cells(10, 4).Select
                Selection.Copy
                wb.Worksheets("Transactions").Activate
                lastrow2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
                ActiveSheet.Cells(lastrow2 + 1, 3).Select
                ActiveCell.PasteSpecial xlPasteValues
                'Copy & Paste Associate Name To Another Workbook
                ThisWorkbook.Activate
                ThisWorkbook.Worksheets("Allocation").Cells(10, 7).Select
                Selection.Copy
                wb.Worksheets("Transactions").Activate
                lastrow3 = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
                ActiveSheet.Cells(lastrow3 + 1, 4).Select
                ActiveCell.PasteSpecial xlPasteValues
                'Copy & Paste Allocation To Another Workbook
                ThisWorkbook.Activate
                ThisWorkbook.Worksheets("Allocation").Cells(12, 4).Select
                Selection.Copy
                wb.Worksheets("Transactions").Activate
                lastrow3 = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
                ActiveSheet.Cells(lastrow3 + 1, 5).Select
                ActiveCell.PasteSpecial xlPasteValues
                Columns("B:J").Sort Key1:=Range("F2"), Order1:=xlDescending, Key2:=Range("G2") _
                , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
                , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
                xlSortNormal
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            Else
                If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "Yes" Then
                    'Copy & Paste Key No. To Another Workbook
                    ThisWorkbook.Activate
                    ThisWorkbook.Worksheets("Allocation").Cells(8, 4).Select
                    Selection.Copy
                    wb.Worksheets("Transactions").Activate
                    lastrow1 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
                    ActiveSheet.Cells(lastrow1 + 1, 2).Select
                    ActiveCell.PasteSpecial xlPasteValues
                    'Copy & Paste Associate ID To Another Workbook
                    ThisWorkbook.Activate
                    ThisWorkbook.Worksheets("Allocation").Cells(10, 4).Select
                    Selection.Copy
                    wb.Worksheets("Transactions").Activate
                    lastrow2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
                    ActiveSheet.Cells(lastrow2 + 1, 3).Select
                    ActiveCell.PasteSpecial xlPasteValues
                    'Copy & Paste Associate Name To Another Workbook
                    ThisWorkbook.Activate
                    ThisWorkbook.Worksheets("Allocation").Cells(10, 7).Select
                    Selection.Copy
                    wb.Worksheets("Transactions").Activate
                    lastrow3 = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
                    ActiveSheet.Cells(lastrow3 + 1, 4).Select
                    ActiveCell.PasteSpecial xlPasteValues
                    'Copy & Paste Allocation To Another Workbook
                    ThisWorkbook.Activate
                    ThisWorkbook.Worksheets("Allocation").Cells(12, 4).Select
                    Selection.Copy
                    wb.Worksheets("Transactions").Activate
                    lastrow3 = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
                    ActiveSheet.Cells(lastrow3 + 1, 5).Select
                    ActiveCell.PasteSpecial xlPasteValues
                    Columns("B:J").Sort Key1:=Range("F2"), Order1:=xlDescending, Key2:=Range("G2") _
                    , Order2:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
                    , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
                    xlSortNormal
                    ActiveWorkbook.Save
                    ActiveWorkbook.Close
                Else
                    If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "" Then
                        ActiveWorkbook.Save
                        ActiveWorkbook.Close
                        MsgBox "Key Already Allocated"
                    End If
                End If
            End If
    End If
End If
MsgBox "Key - " & Cells(8, 4) & " Allocated To - " & Cells(10, 7).Value & " - " & Cells(10, 4)
Range("D8").ClearContents
Range("D10").ClearContents

End Sub

3rd one is for return
Sub Retrn()

Dim wb As Workbook
Dim Key As Variant

If Range("D8") = "" Then
    MsgBox "Please Input Key No."
Exit Sub
Else
    Key = Range("D8")
    Set wb = Workbooks.Open(ThisWorkbook.Worksheets("Employee Addition").Range("H1") & "Database.xlsm")
    wb.Worksheets("Transactions").Activate
    Range("B1").Select
    Set Rng = Cells.Find(What:=Key, _
    After:=ActiveCell, _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
        If Rng Is Nothing Then
            MsgBox "Key Is Not Allocated"
        Else
            If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "Yes" Then
                MsgBox "Key Is Not Allocated"
            Else
                If Rng = Key And Cells(Rng.Row, 5) = "Yes" And Cells(Rng.Row, 8) = "" Then
                    'Copy & Paste Key No. To Another Workbook
                    ActiveSheet.Cells(Rng.Row, 3).Select
                    Selection.Copy
                    ThisWorkbook.Activate
                    ThisWorkbook.Worksheets("Allocation").Cells(10, 4).Select
                    ActiveCell.PasteSpecial xlPasteValues
                    ThisWorkbook.Worksheets("Allocation").Cells(12, 4).Select
                    Selection.Copy
                    wb.Worksheets("Transactions").Activate
                    ActiveSheet.Cells(Rng.Row, 8).Select
                    ActiveCell.PasteSpecial xlPasteValues
                    ActiveWorkbook.Save
                    ActiveWorkbook.Close
                End If
            End If
        End If
End If
MsgBox "Key - " & Cells(8, 4) & " Returned By - " & Cells(10, 7).Value & " - " & Cells(10, 4)
Range("D8").ClearContents
Range("D10").ClearContents

End Sub

Need help to convert all these to Libre Office Macros at the given Version: 6.3.5.2.

Macro to Copy, Paste in another file at given location, Save, Close in Libre Office Version: 6.3.5.2
#2

No one is going to re-write your excel macros for you. Its a bit of a job.
Can you please explain where it fails and what the error message is?

#3

Yes, if the first one is done then I can rewrite all by myself…it sticks where there is copy…open a xlsm file at a path…paste data in it…save the file and close…if this much is done…i can rewrite everything…but version definetly matters of libreoffice

#4

Quoting @Neil123456: “Yes, if the first one is done then I can rewrite all by myself…”
Bet you can’t.
The ways native macros are written for LibreOffice are completely different from the ways gone in VBA. For anything actually needing access to the document (the objects it consists of) you have to use the API and uno-objects. This is a powerful system, but not well documented for beginners - and (hopefully?) there are no youtube videos about it.
LibreOffice simply doesn’t encourage end users to create a macro every next hour, but to use the powerful ready-made means it provides.
This may partly be due to the fact that the documentfoundation doesn’t need to lock-in users to perpetuate profits.

Back to the case: Either your VBA code works under Option VBAsupport 1 or you need to go a different way.

#5

Ok…let me change my question then…i would just need help to get a macro which can copy cell data from this workbook…open another one and paste it dynamically…save the file and save and close it…the one that has macro should remain open

#6

Yes, your first macro does just that. What happens if you complete the Add () procedure several times? You will add several identical values ​​to the end of the “Employee Details” table, right?

What is the name of Sheet3? (In one place, a macro accesses a worksheet by name, in another by its internal name. It looks careless)

#7

Yes, identical values are fine. Sheet 3 can be anyname no issue with names. Here the simple thing is to copy cell data…open another file and paste cell data…finally save and close the file where data is pasted. Finally clear cell from where data was copied. I am not too tech savy

#8

Well, wait a little longer - I will try to show how the Add() procedure should look in terms of StarBasiс

#9

Sure…thanks for all the help

#10

@JohnSUN were you able to achieve it??

#11

Can anyone help me with the Add() procedure only?? Please… I am trying my level best but not achieve what is needed. I get stuck with opening another file and copy from one cell into the new file cell and even save and close.

#12

Hi…I came to a point where half of the functionality is working; however, I am stuck with the ActiveSheet function for lastrow1 as it is not working properly. Hence can’t move ahead…if someone can help me…I know i am not very good with macros but here is what I have achieved

#13

Sub Add()

Dim wb As Workbook

If Sheet3.Range("D6").Value = "" Then
    MsgBox "Please add Associate ID into Database"
Exit Sub
Else
    If Sheet3.Range("D8").Value = "" Then
        MsgBox "Please add Associate Name into Database"
    Exit Sub
    Else
        ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
        ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Copy
        Set wb = Workbooks.Open(Range("H1") & "Database.xlsm")
        wb.Worksheets("Employee Details").Activate
        lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Cells(lastrow1 + 1, 1).Select
        ActiveCell.PasteSpecial xlPasteValues
        ThisWorkbook.Activate
        ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
        Selection.Copy
        wb.Worksheets("Employee Details").Activate
#14

lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow1 + 1, 1).Select
ActiveCell.PasteSpecial xlPasteValues
ThisWorkbook.Activate
ThisWorkbook.Worksheets(“Employee Addition”).Cells(8, 4).Select
Selection.Copy
wb.Worksheets(“Employee Details”).Activate
lastrow2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Cells(lastrow2 + 1, 2).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
End If
End Sub

#15

For starters, forget the VBA terminology - here it will bother you. Forget Select before Copy or PasteSpecial, forget ActiveSheet, forget .End(xlUp) method. In this BASIC, you don’t need to copy anything to the clipboard and then switch to the target sheet and paste the value in the right place.

Of course you can get ActiveSheet. For example, so oActiveSheet = ThisComponent.getCurrentController().getActiveSheet() or so oActiveSheet = ThisComponent.getCurrentSelection().getSpreadsheet()

But why do you need this uncertainty? You have all the sheets with predefined names - get the desired sheet using the ThisComponent.getSheets().getByName("…") method.

#16

The Add() procedure can be rewritten in this way

(I tried to maintain the initial sequence of statements so that it would be easier for you to understand the code. If clarification is required, you can ask clarifying questions in the comments)

Option Explicit

Sub newAdd()
Dim oSheet As Variant
Dim oDoc As Variant, wasNotOpened As Boolean 
Dim outSheet As Variant, oCellByPosition As Variant, LastRow As Long
Dim oCellD6 As Variant, oCellD8 As Variant, valH1 As String, DBFileName As String 
	GlobalScope.BasicLibraries.loadLibrary("Tools")
	oSheet = ThisComponent.getSheets().getByName("Employee Addition")
	valH1 = GetStringOfCellbyName(oSheet, "H1")
	If Right(valH1,1) <> GetPathSeparator() Then valH1 = valH1 & GetPathSeparator()
	DBFileName = ConvertToURL(valH1 & "Database.xlsm")
	If Not FileExists(DBFileName) Then 
		MsgBox("Database workbook " & ConvertFromURL(DBFileName) & " not found", 0, "Wrong path to database")
		Exit Sub 
	EndIf 
	oCellD6 = oSheet.getCellRangeByName("D6")
	If Trim(oCellD6.getString()) = "" Then
		MsgBox("Please add Associate ID into Database", 0, "Wrong ID")
		Exit Sub 
	EndIf 
	oCellD8 = oSheet.getCellRangeByName("D8")
	If Trim(oCellD8.getString()) = "" Then
		MsgBox("Please add Associate Name into Database", 0, "Wrong Name")
		Exit Sub 
	EndIf 
	oDoc = OpenDocument(DBFileName, Array(), wasNotOpened)
	outSheet = oDoc.getSheets().getByName("Employee Details")
	LastRow = GetLastUsedRow(outSheet) + 1
	outSheet.getCellByPosition(0, LastRow).setValue(oCellD6.getValue())
	outSheet.getCellByPosition(1, LastRow).setString(oCellD8.getString())
	oDoc.store
	If wasNotOpened Then oDoc.close(True)
	oCellD6.clearContents(7)
	oCellD8.clearContents(7)
End Sub

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.