Help converting excel Macro

Creating a macro - Writing a Script - Using the API

Help converting excel Macro

Postby kdsmds » Thu Feb 21, 2008 6:12 am

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   Expand viewCollapse view
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
kdsmds
 
Posts: 1
Joined: Thu Feb 21, 2008 6:01 am

Re: Help converting excel Macro

Postby pitonyak » Thu Feb 21, 2008 10:50 pm

I edited your post to place your code into a code block. Does this little snippet get you started?
Code: Select all   Expand viewCollapse view
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
pitonyak
Volunteer
 
Posts: 186
Joined: Sun Oct 07, 2007 9:13 pm
Location: Columbus, Ohio, USA


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests