Practical Web Programming

Sunday, December 09, 2007

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

2 comments:

Anonymous said...

Very good!

Anonymous said...

Hi all.

Is it possible to convert this code to VBA?
I don't know how to manage flexigrid.

Thanks.

Recent Post