Help converting excel Macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
kdsmds
Posts: 1
Joined: Thu Feb 21, 2008 6:01 am

Help converting excel Macro

Post by kdsmds »

Hello I am new to Openoffice.org basic and I cannot figure out how to convert my code that I wrote for Excel to OOO. Any help would be helpful.

My macros should do the following. When you enter a number, it should bring up msgbox that says sure right qty and part used yes/no, if yes then recalculates, (auto calc is off)

Here is my excel macros i for used parts and one for recieved

Code: Select all

Private Sub Worksheet_change(ByVal Target As Range)
    

'used parts

x = 9
y = 7

Do

    z = Worksheets("sheet1").Cells(x, y)
    
    x = x + 1
    
Loop Until z > 0 Or x = 3500
    
    If z > 0 Then
        
        currentitem = Worksheets("sheet1").Cells(x - 1, y - 3)
        currentqty = Worksheets("sheet1").Cells(x - 1, y)
        currentdesc = Worksheets("sheet1").Cells(x - 1, y - 2)
        
        If IsNumeric(currentqty) = True Then
        
            response = MsgBox("Did you use (" & currentqty & ") MANF. # " & currentitem & vbCrLf & vbCrLf & currentdesc & "?", vbYesNo)
        
            If response = vbYes Then
        
                Calculate
                Worksheets("sheet1").Cells(x - 1, y) = ""
                          
                response = MsgBox("You have successfully updated your inventory list. Do you need to Use/Recieve any more items?", vbYesNo)
                    
                    If response = vbNo Then
                        ActiveWorkbook.Save
                        ActiveWorkbook.Close
                    End If
                    
            End If
            If response = vbNo Then
                Worksheets("sheet1").Cells(x - 1, y) = ""
            End If
            
            
        Else

            MsgBox "You must enter a number!!", vbOKOnly
            Worksheets("sheet1").Cells(x - 1, y) = ""
            
        End If
        
    End If
    
    
    
'recieved parts

 x = 9
 y = 9
 Do

    z = Worksheets("sheet1").Cells(x, y)
    
    x = x + 1
    
Loop Until z > 0 Or x = 3500
    
    If z > 0 Then
        currentitem = Worksheets("sheet1").Cells(x - 1, y - 5)
        currentqty = Worksheets("sheet1").Cells(x - 1, y)
        currentdesc = Worksheets("sheet1").Cells(x - 1, y - 4)
        
        If IsNumeric(currentqty) = True Then
            response = MsgBox("Did you recieve (" & currentqty & ") MANF. # " & currentitem & vbCrLf & vbCrLf & currentdesc & "?", vbYesNo)
            If response = vbYes Then
        
                Calculate
                Worksheets("sheet1").Cells(x - 1, y) = ""
                response = MsgBox("You have successfully updated your inventory list. Do you need to Use/Recieve any more items?", vbYesNo)
                    If response = vbNo Then
                        ActiveWorkbook.Save
                        ActiveWorkbook.Close
                    End If
                    
            End If
                If response = vbNo Then
                    Worksheets("sheet1").Cells(x - 1, y) = ""
                End If
            
            
            Else

                MsgBox "You must enter a number!!", vbOKOnly
                Worksheets("sheet1").Cells(x - 1, y) = ""
        End If
      
    End If
    

End Sub
pitonyak
Volunteer
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA

Re: Help converting excel Macro

Post by pitonyak »

I edited your post to place your code into a code block. Does this little snippet get you started?

Code: Select all

REM The argument "Target" is never used
REM Private Sub Worksheet_change(ByVal Target As Range)
REM OK, so what document are you using? I will assume
REM the current document.
Sub Worksheet_change()
'used parts

  Dim x As Long
  Dim y As Long
  Dim z As Double
  Dim oSheet
  
  x = 9
  y = 7

  oSheet = ThisComponent.Sheets.Sheet1
  Do
    REM This is NOT efficient, but it is a direct translation.
    REM I do not know if x is row or column, so I assumed that
    REM x is the row...
    'z = Worksheets("sheet1").Cells(x, y)
    z = oSheet.getCellByPosition(y, x).Value
    x = x + 1
  Loop Until z > 0 Or x = 3500

  If z > 0 Then
       
    'currentitem = Worksheets("sheet1").Cells(x - 1, y - 3)
    'currentqty = Worksheets("sheet1").Cells(x - 1, y)
    'currentdesc = Worksheets("sheet1").Cells(x - 1, y - 2)
    currentitem = oSheet.getCellByPosition(y-3, x-1).Value
    currentqty = oSheet.getCellByPosition(y, x-1).Value
    currentdesc = oSheet.getCellByPosition(y-2, x-1).Value
Andrew Pitonyak
http://www.pitonyak.org/oo.php
LO and AOO on Fedora
Post Reply