How To Export To OpenOffice Spreadsheet (Calc) in VB6

Exporting data to MS Excel is relatively easy. But if you are an advocate of Opensource software, you maybe using OpenOffice instead of Microsort Office.

This visual basic source codes show how to export the contents of the MSFlexGrid into OpenOffice Calc spreadsheet.

To test this source codes, open a Visual Basic 6 project add the ff. controls to a form: an MSFlexGrid, a CommandButton. Then, copy and paste the source codes to your project and you're good to go.

Public Sub ExportGridToCalc(grdTemp As MSFlexGrid, _
                strTitle As String, _
                Optional lngStartRow As Long = -1, _
                Optional lngEndRow As Long = -1, _
                Optional lngStartCol As Long = -1, _
                Optional lngEndCol As Long = -1)

  Dim oSM         As Object 'Object for accessing OpenOffice
  Dim oDesk       As Object 'Objects from the API
  Dim oDoc        As Object 'Objects from the API
  Dim oSheet      As Object 'Objects from the API
  Dim arg()                 'Ignore it for the moment !
  Dim lngRow      As Long
  Dim lngCol      As Long

  '-->SET FIRST THE ROWS AND COLS
  If lngStartRow = -1 Then lngStartRow = 1
  If lngEndRow = -1 Then lngEndRow = grdTemp.Rows - 1
  If lngStartCol = -1 Then lngStartCol = 0
  If lngEndCol = -1 Then lngEndCol = grdTemp.Cols - 1

  'Instanciate OOo : this line is mandatory with VB for OOo API
  Set oSM = CreateObject("com.sun.star.ServiceManager")
  'Create the first and most important service
  Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
  'Create a new doc
  Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", _
                       0, arg())
  'Get the first sheet in the doc
  Set oSheet = oDoc.getSheets().getByIndex(0)

  Call oSheet.getCellByPosition(0, 0).setString(strTitle)
  For lngRow = lngStartRow To lngEndRow
    For lngCol = lngStartCol To lngEndCol
      Call oSheet.getCellByPosition(lngCol, lngRow + _
      IIf(lngStartRow = 0, 2, 1)).setString(grdTemp.TextMatrix(lngRow, _
                          lngCol))
    Next
  Next
End Sub

Private Sub LoadDataToGrid()
  Dim lngRow        As Long
  Dim lngCol        As Long

  With MSFlexGrid1
    .Rows = 11
    .Cols = 5
    .FixedCols = 0

    For lngRow = 0 To 5 - 1
      .TextArray(lngRow) = "Header " & lngRow + 1
    Next

    For lngCol = 0 To 5 - 1
      For lngRow = 1 To 10
        .TextMatrix(lngRow, lngCol) = "R " & lngRow & " - C " & lngCol + 1
      Next
    Next
  End With
End Sub

Private Sub Command1_Click()
  Call ExportGridToOOCalc(MSFlexGrid1, "Exporting to OpenOffice")
End Sub

Private Sub Form_Load()
  Call LoadDataToGrid
End Sub