OpenOffice Calc macro: Save

Sub Main 

   ' Prepare the filename to save. 
   cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" ' Windows 
'   cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux 

   ' Now save the spreadsheet. 
   cURL = ConvertToURL( cFile + ".sxc" ) 
   oDoc.storeAsURL( cURL, Array() ) 

   ' Note the above used storAsUrl, the following use storeToUrl. 

   ' Now save it in Excel format. 

   cURL = ConvertToURL( cFile + ".xls" ) 
   oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "MS Excel 97" ) ) ) 

   ' Now save a PDF. 
   cURL = ConvertToURL( cFile + ".pdf" ) 
   oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "calc_pdf_Export" ) ) ) 

   ' Now save as HTML. 
   cURL = ConvertToURL( cFile + ".html" ) 
   oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "HTML (StarCalc)" ) ) )

 End Sub

'---------- 
'   Create and return a new com.sun.star.beans.PropertyValue. 
' 
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue 
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue 
   If Not IsMissing( cName ) Then 
      oPropertyValue.Name = cName 
   EndIf 
   If Not IsMissing( uValue ) Then 
      oPropertyValue.Value = uValue 
   EndIf 
   MakePropertyValue() = oPropertyValue 
End Function

Reference:

Question: How would I add or delete Sheets using Macro…

OpenOffice Calc macro: Add/Remove sheet

Sub Main 
    
   '----- 
   ' Insert six more sheets. 
   nNumSheetsCurrently = oDoc.getSheets().getCount() 
   oDoc.getSheets().insertNewByName( "Fred", nNumSheetsCurrently+1 ) 
   oDoc.getSheets().insertNewByName( "Joe", nNumSheetsCurrently+2 ) 
   oDoc.getSheets().insertNewByName( "Bill", nNumSheetsCurrently+3 ) 
   oDoc.getSheets().insertNewByName( "Sam", nNumSheetsCurrently+4 ) 
   oDoc.getSheets().insertNewByName( "Tom", nNumSheetsCurrently+5 ) 
   oDoc.getSheets().insertNewByName( "David", nNumSheetsCurrently+6 ) 
   ' Now find a sheet named "Sheet2" and get rid of it. 
   oDoc.getSheets().removeByName( "Sheet2" ) 
   '----- 
   
 End Sub

Reference:

Question: How would I add or delete Sheets using Macro…

OpenOffice Calc macro: search a cell

Sub Find
    sAns = InputBox("Enter what to find below.")
    If sAns = "" then End 'Blank entry or Cancel clicked.
    oDoc = ThisComponent
    oCell = oDoc.CurrentSelection 'Get active sheet index.
    index = oCell.CellAddress.Sheet
    oSheet = oDoc.getSheets.getByIndex(Index)'Get active sheet.

    FandR = oSheet.createSearchDescriptor 'Set up find and replace.
    FandR.setSearchString(sAns)
    'FandR.SearchWords = true 'Entire cell must match.
    oCell = oSheet.findFirst(FandR)
    If isNull(oCell) then
    endif

    MsgBox "Row = " & oCell.CellAddress.Row & Chr(13) & "Column = " &
    oCell.CellAddress.Column
    Else MsgBox "Not Found!"
    EndIf
End Sub

Reference:

Return column and row using Calc search in a macro

OpenOffice Calc macro: search and replace cell from lookup table

 

Sub FandRCond 

Dim oSheet1, oSheet2, oCell, oRange1, oRange, oCellName1, oCellName2 
Dim ca as Integer, cb as Integer, cc as Integer, c as Integer, r as Integer 

oSheet1 = thisComponent.Sheets(0) 
oSheet2 = thisComponent.Sheets(1) 
oRange1 = oSheet1.getCellRangeByPosition(0,0,3,9) 
oRange2 = oSheet2.getCellRangeByPosition(0,0,3,3) 
FandR = oRange1.createReplaceDescriptor 
FandR.SearchWords = true 

ca = 0 
cb = 1 
cc = 2 

for r = 0 to 3 

oCell = oRange2.getCellByPosition(ca, r) 

if oCell.Value = 1 then 

oCellName1 = oRange2.getCellByPosition(cb, r) 
oCellName2 = oRange2.getCellByPosition(cc, r) 
fString = oCellName1.String 
rString = oCellName2.String 

FandR.setSearchString(fString) 
FandR.SetReplaceString(rString) 
oRange1.FindAll(FandR) 

oRange1.ReplaceAll(FandR) 

end if 

next r 

End Sub

Reference:

CALC: macro for “search and replace” func

OpenOffice Calc macro: set cell content

The com.sun.star.table.XCell interface provides six methods :

  1. getFormula returns the formula string of a cell.
  2. setFormula sets a formula into the cell.
  3. getValue returns the floating point value of the cell.
  4. setValue sets a floating point value into the cell.
  5. getType returns the type of the cell.
  6. getError returns the error value of the cell.

Only when you are setting number into a cell, you use .getValue() .setValue(); otherwise, always use .getFormula() .setFormula()

Sub Main 
   '----- 
   ' Put some sales figures onto the sheet. 
   oSheet.getCellByPosition( 0, 0 ).setFormula( "Month" ) 
   oSheet.getCellByPosition( 1, 0 ).setFormula( "Sales" ) 

   oSheet.getCellByPosition( 0, 1 ).setFormula( "Jan" ) 
   oSheet.getCellByPosition( 0, 2 ).setFormula( "Feb" ) 
   oSheet.getCellByPosition( 0, 3 ).setFormula( "Mar" ) 
   oSheet.getCellByPosition( 0, 4 ).setFormula( "Apr" ) 
   oSheet.getCellByPosition( 0, 5 ).setFormula( "May" ) 
   oSheet.getCellByPosition( 0, 6 ).setFormula( "Jun" ) 

   oSheet.getCellByPosition( 1, 1 ).setValue( 3826 ) 
   oSheet.getCellByPosition( 1, 2 ).setValue( 3504 ) 
   oSheet.getCellByPosition( 1, 3 ).setValue( 2961 ) 
   oSheet.getCellByPosition( 1, 4 ).setValue( 2504 ) 
   oSheet.getCellByPosition( 1, 5 ).setValue( 2102 ) 
   oSheet.getCellByPosition( 1, 6 ).setValue( 1756 ) 
   '-----
 End Sub

To reference cell by name, use getCellRangeByName()

Reference:

Question: How would I add or delete Sheets using Macro…

http://fr.wikibooks.org/wiki/OpenOffice.org/Calc/Macros

OpenOffice Calc macro: Set oDoc to file we’re working with.

Sub Main 
   ' Set oDoc to which spreadsheet document we're working with.    
   ' Three examples of how to set the variable oDoc... 

   '----- 
   ' In this case, work with the document that this macro is embedded into. 
'   oDoc = ThisComponent 
   '----- 

   '----- 
   ' Use this line instead to create a NEW calc document. 
   oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() ) 
   '----- 

   '----- 
   ' Use this instead to open an EXISTING calc document. 
'   cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" ' Windows 
'   cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux 
'   cURL = ConvertToURL( cFile + ".sxc" ) 
'   oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, Array() ) 
   '-----
End Sub

Reference:

Question: How would I add or delete Sheets using Macro…