Using VB.Net to Update OpenOffice Calc Spreadsheet (without leaving .bin file hanging!)

April 25, 2021 9:45 am by ManFridayIT

Update Open Office Calc Spreadsheet in VB.Net

Most of the references I have come across for updating Open Office with VB.Net do not cover closing the application (or in some cases opening) completely. Unlike MS Office automation where quitting / closing the class object and setting it to nothing clears the application from the process stack, OpenOffice has a tendency to loiter about especially with a stubborn .bin file.
The following code in VB.Net shows how to open, update, and close an existing OpenOffice Calc sheet. The process opens the worksheet, finds the first completely empty row and inserts values into a row of cells, saves the OpenOffice Calc sheet and closes the whole application. Unlike the MSOfficeExcel example it cannot work if other instances of the application are open, in which case it will notify and back out.

Private Sub OO()

Dim oSheet As Object
Dim oSM ‘Root object for accessing OpenOffice from VB
Dim oDesk, oDoc As Object ‘First objects from the API
Dim arg(1) As Object ‘Used as parameter in opening doc
Dim lastRow As Long
Dim rowIndex As Long
Dim c As Object

Check to see if OO instance open anywhere, if so notify and back out
Dim oOprocess() as Process
oOProcess = Process.GetProcessesByName ("SOffice")
If oOProcess.Length > 0 Then
MsgBox("Unable to proceed whilst other Open Office programs are running. Close the programs, wait for a short while and re-try.")
Exit Sub
End If
Try
oSM = CreateObject ("com.sun.star.ServiceManager") ‘Instantiate Open Office for VB

oDesk = oSM.createInstance ("com.sun.star.frame.Desktop") ‘Create service

‘Open an existing doc, note no index on arg parameter
oDoc = oDesk.loadComponentFromURL ("file:///C:/Users/Public/Documents/Test/OD_Test.ods", "_blank", 0, arg)

Catch ex As Exception
‘Tidy up in case of partially opened/hanging OO instance
Dim oProcess() As Process
oProcess = Process.GetProcessesByName ("SOffice.bin")
If oProcess.Length > 0 Then
oProcess(0).Kill()
End If
MessageBox.Show("Error " & ex.ToString)
Exit Sub
End Try

oSheet = oDoc.getSheets().getByIndex(0) ‘Get first sheet
c = oSheet.createCursor
c.gotoEndOfUsedArea(False) ‘Points cursor to the end of the used area, parameter sets cursor size to single cell
lastRow = c.RangeAddress.EndRow ‘Get index of last used row – returns zero based index
rowIndex = lastRow + 1
Call oSheet.getCellByPosition(0, rowIndex).SetString("Col1") ‘Enter string value in selected cells(Col, Row) in first empty row
Call oSheet.getCellByPosition(1, rowIndex).SetString("Col2")
‘Cell Entry Options –
To Enter Numbers - SetValue(123) ;
To Enter Characters - SetString(“ABC”) ;
To Enter A Formula - SetFormula(“=SUM(A1:E4)”)
Call oSheet.getCellByPosition(2, rowIndex).SetString("Col3")
Call oSheet.getCellByPosition(3, rowIndex).SetString("Col4")
Try
‘Save the doc
Call oDoc.store()
‘Tidy open objects
oDoc.Close(True)
oDoc = Nothing
oDesk = Nothing
oSM = Nothing

Catch ex As Exception
‘Tidy up in case of partially opened/hanging OO instance
Dim oProcess() As Process
oProcess = Process.GetProcessesByName ("SOffice.bin")
If oProcess.Length > 0 Then
oProcess(0).Kill()
End If
MessageBox.Show("Error " & ex.ToString)
Exit Sub
End Try

‘Close Open Office (process name SOffice)
Dim oProcess1() As Process
oProcess1 = Process.GetProcessesByName ("SOffice")
oProcess1(0).Kill()

‘Tidy up process close
GC.Collect() ‘Force garbage collection
GC.WaitForPendingFinalizers() ‘Wait for end of garbage collection before continuing
GC.Collect() ‘ Second pass of cleaning
GC.WaitForPendingFinalizers()

‘Closing/Killing the Open Office program in vb.net leaves an SOffice.bin file open which prevents user from opening an instance of OpenOffice
Dim oProcess2() As Process
oProcess2 = Process.GetProcessesByName ("SOffice.bin")
oProcess2(0).Kill()

‘ Tidy up process close
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

MsgBox(“Finished”)
End Sub

This works OK with Open Office 4.1.5, but care should be taken when opening OpenOffice after closing it, despite being put through Garbage Collection the program loiters invisibly for about 20 seconds after being closed. This little quirk appears to be an ongoing ‘feature’ of OO, I see the problem being reported back a number of years.
I tried every combination (many times!) of closing the SOffice application cleanly without the need to ProcessKill, but an SOffice.bin file was always left open. This would not interfere in the automation of Calc with VB.Net but if an Open Office program was manually instantiated on the desktop then the open process would hang and fail.

The above code can be seen in use in the following video, where a vb.net application sweeps a Gmail account (using the Gmail API) looking for a specific website form, parsing the contents and updating desktop applications ( an Access database, Excel Spreadsheet and OpenOffice Calc sheet) with the contents of the email.

Creating bespoke office systems and business websites in Derby are some of ManFridayIT’s services. For further information email info@manfridayit.co.uk

---