Excel macro problem in OpenOffice

[Sepjyp2013]

Hello
Good Day

Thanks for joining me in your group and i glad to here

first of all iwould like to tell that is i am totally Newb for this forum and for Open office also, recently my boss install the OpenOffice Package and he want to do all the excel related works in this package and we start work without any problem. Now i am facing one problem that is i was created one macro file in excel file and we want to use the file in Ooo so what i did i copy the module code and paste in Ooo module and i press F5 i didnot get any error but when i paste another code i got this error that is BASIC syntax error. Public not allowed with in a procedure. for you better knowlede i will give my excel code

Code: Select all

Option Explicit

Public MAX ROWS As Integer

Public Sub setMAX_ROWS()
          Module1.MAX_ROWS=getMaxRows
End Sub

Note getMaxRows is Function macro

Hope i clearly explained if not then please ask me i will explain again with your req. ment

Thanking You

Patnaik

[Charlie Young]

I thing you need to post your getMaxRows() function before we can see the problem.

[Sepjyp2013]

Hello

Good Day

Thanks for your quick replay, for your better knowledge i will give my all codes please check

The below mention codes are in Excel

<strong>This is the Workbook Module Code</strong>

`

Code: Select all

Private Sub Workbook_Open()
    Module1.setMAX_ROWS
End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Module1.setMAX_ROWS
End Sub
`
The below mention code is Module1

`
Option Explicit

Public MAX_ROWS As Integer

Public Sub setMAX_ROWS()
    Module1.MAX_ROWS = getMaxRows
End Sub

Function getMaxRows()
    Dim maxRows As Double: maxRows = 0
    Dim employeesRows As Double: employeesRows = Sheet2.Range("A1").End(xlDown).Row + 1
    Dim gaugesRows As Double: gaugesRows = Sheet3.Range("A1").End(xlDown).Row + 1
    Dim checkoutsRows As Double: checkoutsRows = Sheet4.Range("A1").End(xlDown).Row + 1
    If employeesRows > gaugesRows Then
        maxRows = employeesRows
    Else
        maxRows = gaugesRows
    End If
    
    If maxRows < checkoutsRows Then
        maxRows = checkoutsRows
    End If
    
    getMaxRows = maxRows
End Function

Sub LookupEmployee()
    Worksheets("main").Range("B3").Value = ""
    Worksheets("main").Range("B4").Value = ""
    Worksheets("main").Range("B5").Value = ""
    Dim employeeID As String: employeeID = Worksheets("main").Range("B2").Value
    Dim Rng As Range
    If Trim(employeeID) <> "" Then
        With Sheets("employees").Range("A2:A" & MAX_ROWS)
            Set Rng = .Find(What:=employeeID, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Rng Is Nothing Then
                MsgBox "Employee not found"
            Else
                Dim row2update As Integer: row2update = Rng.Cells(1, 1).Row
                Worksheets("main").Range("B3").Value = Sheets("employees").Range("B" & row2update).Value 'employee First Name
                Worksheets("main").Range("B4").Value = Sheets("employees").Range("C" & row2update).Value 'employee Last Name
                Worksheets("main").Range("B5").Value = Sheets("employees").Range("D" & row2update).Value 'employee Period
            End If
        End With
    End If
End Sub
Sub LookupGuages()
    Worksheets("main").Range("B8").Value = ""
    Worksheets("main").Range("B9").Value = ""
    Worksheets("main").Range("B10").Value = ""
    Dim guageID As String: guageID = Worksheets("main").Cells(7, 2).Value
    Dim Rng As Range
    If Trim(guageID) <> "" Then
        With Sheets("Guages").Range("A1:A" & MAX_ROWS)
            Set Rng = .Find(What:=guageID, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Rng Is Nothing Then
                MsgBox "Guage not found"
            Else
                Dim row2update As Integer: row2update = Rng.Cells(1, 1).Row
                Worksheets("main").Cells(8, 2).Value = Sheets("Guages").Range("B" & row2update).Value  'Guage Product Number
                Worksheets("main").Cells(9, 2).Value = Sheets("Guages").Range("C" & row2update).Value  'Guage Location
                Worksheets("main").Cells(10, 2).Value = Sheets("Guages").Range("D" & row2update).Value 'Guage Lexile
            End If
        End With
    End If
End Sub

Sub CheckoutGuages()
    Dim employeeID As String: employeeID = Worksheets("main").Range("B2").Value
    Dim employeeFirstName As String: employeeFirstName = Worksheets("main").Range("B3").Value
    Dim employeeLastName As String: employeeLastName = Worksheets("main").Range("B4").Value
    Dim employeePeriod As String: employeePeriod = Worksheets("main").Range("B5").Value
    Dim guageID As String: guageID = Worksheets("main").Range("B7").Value
    Dim productNumber As String: productNumber = Worksheets("main").Range("B8").Value
    Dim guageLexile As String: guageLexile = Worksheets("main").Range("B10").Value
    Dim checkoutDuration As Integer: checkoutDuration = Worksheets("main").Range("$F$2").Value
    Dim DupRng As Range
    Dim Rng As Range

    If checkoutDuration < 1 Then
        MsgBox ("Checkout duration cannot be less than 1 day")
    ElseIf checkoutDuration > 365 Then
        MsgBox ("Checkout duration cannot be more than 365 days")
    ElseIf employeeFirstName = "" Then
        MsgBox ("Employee first name is empty")
    ElseIf employeePeriod = "" Then
        MsgBox ("Employee period is empty")
    ElseIf employeeLastName = "" Then
        MsgBox ("Employee last name is empty")
    ElseIf guageLexile = "" Then
        MsgBox ("Guage lexile is empty")
    ElseIf productNumber = "" Then
        MsgBox ("Guage title is empty")
    ElseIf guageID = "" Then
        MsgBox ("Guage ID is empty")
    Else
        Dim checkOutID As String: checkOutID = guageID & employeeID
        With Sheets("checkouts").Range("M2:M" & MAX_ROWS)
            Set DupRng = .Find(What:=checkOutID, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
        If DupRng Is Nothing Then
            With Sheets("checkouts").Range("A2:A" & MAX_ROWS)
                Set Rng = .Find(What:="", _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                                
                Dim row2update As Integer: row2update = Rng.Cells(1, 1).Row
                
                Sheets("checkouts").Range("A" & row2update) = guageID
                Sheets("checkouts").Range("B" & row2update) = productNumber
                Sheets("checkouts").Range("C" & row2update) = guageLexile
                
                Sheets("checkouts").Range("D" & row2update) = employeeID
                Sheets("checkouts").Range("E" & row2update) = employeeFirstName
                Sheets("checkouts").Range("F" & row2update) = employeeLastName
                Sheets("checkouts").Range("G" & row2update) = employeePeriod
                Sheets("checkouts").Range("H" & row2update) = Now()
                Sheets("checkouts").Range("I" & row2update) = Now() + checkoutDuration

                MsgBox ("Guage successfully checked out")
            End With
        Else
            MsgBox ("This Employee has already checked out this guage. Checkout failed.")
        End If
    End If
End Sub

Sub ReturnGuage()
    Dim employeeID As String: employeeID = Worksheets("main").Range("B2").Value
    Dim employeeFirstName As String: employeeFirstName = Worksheets("main").Range("B3").Value
    Dim employeeLastName As String: employeeLastName = Worksheets("main").Range("B4").Value
    Dim employeePeriod As String: employeePeriod = Worksheets("main").Range("B5").Value
    Dim guageID As String: guageID = Worksheets("main").Range("B7").Value
    Dim productNumber As String: productNumber = Worksheets("main").Range("B8").Value
    Dim guageLexile As String: guageLexile = Worksheets("main").Range("B10").Value
    Dim DupRng As Range
    Dim Rng As Range
    
    If employeeFirstName = "" Then
        MsgBox ("Employee first name is empty")
    ElseIf employeePeriod = "" Then
        MsgBox ("Employee period is empty")
    ElseIf employeeLastName = "" Then
        MsgBox ("Employee last name is empty")
    ElseIf guageLexile = "" Then
        MsgBox ("Guage lexile is empty")
    ElseIf productNumber = "" Then
        MsgBox ("Guage title is empty")
    ElseIf guageID = "" Then
        MsgBox ("Guage ID is empty")
    Else
        Dim checkOutID As String: checkOutID = guageID & employeeID

        With Sheets("checkouts").Range("M2:M" & MAX_ROWS)
            Set DupRng = .Find(What:=checkOutID, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With

        If DupRng Is Nothing Then
            MsgBox ("This employee has not checked out this guage. Return failed.")
        Else
            Dim row2update As Integer: row2update = DupRng.Cells(1, 1).Row
            Sheets("checkouts").Range("J" & row2update).Value = Now()
            MsgBox ("Guage successfully returned")
        End If
    End If
End Sub


Function encode_ean13(inputstr As String)
  If Len(inputstr) = 12 Then
    Dim i As Integer
    'Check that the input contains only valid characters
    Dim valid As Boolean
    valid = True
    For i = 1 To 12
        If Asc(Mid(inputstr, i, 1)) < 48 Or Asc(Mid(inputstr, i, 1)) > 57 Then
            valid = False
        End If
    Next
    
    If valid Then
        Dim checksum As Integer
        checksum = 0
        
        For i = 2 To 12 Step 2
            checksum = checksum + Val(Mid(inputstr, i, 1))
        Next
        
        checksum = checksum * 3
        
        For i = 1 To 11 Step 2
            checksum = checksum + Val(Mid(inputstr, i, 1))
        Next
        
        inputstr = inputstr & (10 - checksum Mod 10) Mod 10
        
        Dim Barcode As String
        Dim tableA As Boolean
        Dim first As Boolean
        
        Barcode = Left(inputstr, 1) & Chr(65 + Val(Mid(inputstr, 2, 1)))
        first = Val(Left(inputstr, 1))
        
        For i = 3 To 7
            tableA = False
            Select Case i
                Case 3
                    Select Case first
                        Case 0 To 3
                        tableA = True
                    End Select
                Case 4
                    Select Case first
                        Case 0, 4, 7, 8
                        tableA = True
                    End Select
                Case 5
                    Select Case first
                        Case 0, 1, 4, 5, 9
                        tableA = True
                    End Select
                Case 6
                    Select Case first
                        Case 0, 2, 5, 6, 7
                        tableA = True
                    End Select
                Case 7
                    Select Case first
                        Case 0, 3, 6, 8, 9
                        tableA = True
                    End Select
            End Select
            
            If tableA Then
                Barcode = Barcode & Chr(65 + Val(Mid(inputstr, i, 1)))
            Else
                Barcode = Barcode & Chr(75 + Val(Mid(inputstr, i, 1)))
            End If
        Next
        
        Barcode = Barcode & "*"
        For i = 8 To 13
            Barcode = Barcode & Chr(97 + Val(Mid(inputstr, i, 1)))
        Next
        Barcode = Barcode & "+"
        
        encode_ean13 = Barcode
    Else
        encode_ean13 = "Error2"
    End If
    
  Else
    encode_ean13 = "Error1"
  End If
  
End Function

`
In the Module code i copy and paste all in the Ooo file when i press the F5 button then i didn't get any error if i remove the first line upto starting the Function getMaxRows but when i keep the above code then i will get the error

for your full knowledge i attach my files

Excel.xlsm file

https://dl.dropboxusercontent.com/u/756 ... 02013.xlsm

Ooo File

https://dl.dropboxusercontent.com/u/756 ... Method.ods

This is my first post and i am totally Newb for the Ooo so please excuse me if i give you pain,

Thanking You

Patnaik

[RoryOF]

I can't help with your code, but it is important that you tell your boss that OpenOffice _is not_ an Excel clone. The underlying macro language is _very_ different and conversion of macros from Excel to OpenOffice is in general not a trivial task.

[Sepjyp2013]

Hello

Thanks for quick replay

This is my kind request, if any one can do a favor for me

i promise in future i will create the codes own with your given code

Thanking You

Patnaik

[Sepjyp2013]

Hello

Good Day all

Friends is there any chance for me

Thanks

Patnaik

[Zizi64]

Friends is there any chance for me

((...without any sarcasm...))
Yes, there is: you need study the API (application Programming Interface) of the Apache OpenOffice (od LibreOffice) and then you can try rewrite all of those macros...
http://www.openoffice.org/api/
http://api.libreoffice.org/

I (we) can not help you "easy way": "Try this..." or "Try that..." or "Replace a line of the code...".
Your task requires a significant workload.


You can try hire somebody for this task in the "Paid support" forum:
http://forum.openoffice.org/en/forum/viewforum.php?f=53

[Sepjyp2013]

Hello

Good Day All

Thanks for all your support and your valuable replays, as i am learning i changed code like this but i am getting a error Msg that Basic Syntax error: Unexpected Symbol. oSheet what is it's meaning where is the mistake is located

for your better knowledge i attach the code here, please have a look

Code: Select all

Function getMaxRows()
Dim maxRows As Double: maxRows = 0
Dim employeesRows As Double: employeesRows = Sheet2.Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)("A1").End(xlDown).Row + 1
Dim gaugesRows As Double: gaugesRows = Sheet3.dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)("A1").End(xlDown).Row + 1
Dim checkoutsRows As Double: checkoutsRows = Sheet4.Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)("A1").End(xlDown).Row + 1
If employeesRows > gaugesRows Then
maxRows = employeesRows
Else
maxRows = gaugesRows
End If
If maxRows < checkoutsRows Then
        maxRows = checkoutsRows
    End If
    
    getMaxRows = maxRows
End Function

Thanks for your support

Thanks

Patnaik