This is an example of a macro using Open Office. There are a lot of macro functionality that isn’t captured when trying to record a macro. You can add a macro to your menu or tool bar in Open Office by selecting view->tools->customize. From the menus or Toolbars you can select add to add a macro to your menu or toolbar. Under the category, you can select the particular Macro you are adding and select the add button. That command will add your macro to either your menu or your toolbar. This macro will create a new worksheet that has a name of the month and year.

REM ***** BASIC *****
Dim sSheetName As String
Sub insertSheetAsFirst

Dim monthName As String
Select Case month(Date())
Case 1
monthName = “January”
Case 2
monthName = “February”
Case 3
monthName = “March”
Case 4
monthName = “April”
Case 5
monthName = “May”
Case 6
monthName = “June”
Case 7
monthName = “July”
Case 8
monthName = “August”
Case 9
monthName = “September”
Case 10
monthName = “October”
Case 11
monthName = “November”
Case 12
monthName = “December”
Case Else
monthName = “Err”
End Select
monthName=monthName+ ” ” +year(Date())

sSheetName = monthName
If ThisComponent.Sheets().hasByName( sSheetName ) Then
Msgbox “A sheet named: “”" & sSheetName & “”" already exists!”
Else
If ThisComponent.Sheets().getCount() > 255 Then
Msgbox “Maximum number of sheets reached - cannot insert!”
Else
‘ insert as index 0 = first sheet in file
ThisComponent.Sheets().insertNewByName( sSheetName , ThisComponent.Sheets().getCount() +1 )
End If
End If
End Sub

sub CreateNewSheet

call insertSheetAsFirst
oSheet = ThisComponent.Sheets.getByName(sSheetName) ‘GRABS THE WORKSHEET
ThisComponent.CurrentController.setActiveSheet(oSheet)

oSheet.getCellByPosition(0,3).setString(”Password”) ‘SETS STRING
oSheet.getCellByPosition(0,3).CharWeight = 150 ‘BOLDS

oSheet.getCellByPosition(0,9).value=1 ‘SETS A NUMERICAL VALUE
oSheet.getCellByPosition(0,10).value=2

oSheet.getCellByPosition(0,41).setString(”Monthly Totals”)
oSheet.getCellByPosition(0,41).CharWeight = 150
oSheet.getCellByPosition(0,41).CharHeight=22 ‘INCREASES FONT SIZE
oSheet.getCellByPosition(0,41).Columns.Width=6000 ‘INCREASES CELL SIZE

oSheet.getCellByPosition(1,42).Formula=”=SUM(B42:I42)” ‘ADDS A FORMULA TO THE CELL
oSheet.getCellByPosition(1,42).CharWeight = 150
oSheet.getCellByPosition(1,42).CharHeight=22

end sub

Tweet This Post  Plurk This Post  Buzz This Post  Delicious This Post  Digg This Post  Ping This Post  Reddit This Post  Stumble This Post