I have a .xls file I need to import using Lotusscript. Below is the current script I have. It is based on the quoted script which works for .xls files and Excel. Unfortunately, I cannot use Excel on the server. So, I want to use OpenOffice.
My issue is with finding the range. I found information on some OpenOffice forums that has helped a lot. But, how do I declare a cursor in LotusScript? When the script runs right now, I get “automation object argument type mismatch”. Thanks in advance for your help.
Sub Initialize
'from http://www.openntf.org/Projects/codebin/codebin.nsf/CodeSearch/DCD5A132F75581698625726700715FCA
'Based on v 1.2 code from David Moore david.james.moore@gmail.com
'will import from Excel up to 256 columns by 65,536 rows
'Modified by John Tincher
Dim astrFields As Variant
Dim session As New NotesSession
'Only needed when interacting with user
'Dim uiws As New NotesUIWorkspace
Dim form As NotesForm
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim item As NotesItem
Dim row As Integer
Dim xlFilename As String
Dim xlsApp As Variant
Dim xlsWorkBook As Variant
Dim xlsSheet As Variant
Dim rows As Long
Dim cols As Integer
'Dim x As Integer
Dim itemName As String
Dim flag As Integer
Dim formAlias As String
Dim sortEval As String
Dim sortedList As Variant
Dim indexLo As Long
Dim indexHi As Long
Dim fn As Variant
Dim msg As String
Dim i As Long
Dim formname As String
'Added for OpenOffice
Dim objServiceManager As Variant
Dim objDesktop As Variant
Dim worksheet As Variant
Dim oCell As Variant
Dim cursor As Variant
'On Error GoTo ErrorHandler
Set db = session.CurrentDatabase
Print ("Import documents into the database")
formname = "EmployeeProfile"
If formname= "" Then Exit Sub
'Get the form object so that we can check field names
Set form= db.GetForm(formname)
'If the form has an alias, use it to select the form
If Not IsEmpty(form.Aliases) Then formname = form.Aliases(UBound(form.Aliases))
'Adding code for using OpenOffice in place of Excel
'The service manager is always the starting point
'If there is no office running then an office is started up
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
'Create the Desktop
Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
Dim args()
Set xlsApp=objDesktop.loadComponentFromURL("file:///c:/EAData/FlintEA.xls","_default", 0,args)
Print “File is open”
Set xlsSheet=xlsApp.Sheets(0)
Set oCell = xlsSheet.getCellByPosition(0,0)
Set cursor = xlsSheet.createCursorByRange(oCell)
Call cursor.gotoEndOfUsedArea(False)
rows = cursor.getRangeAddress().EndRow
cols = cursor.getRangeAddress().EndColumn
'Make sure we start at row 0
row = 0
'Print “Starting import from xls file…”
Do While True
row = row + 1
'Check to make sure we did not run out of rows
If row= rows+1 Then GoTo Done
'field definitions for notes come from first row (row, column)
If row=1 Then
astrFields = form.Fields
ReDim fd(1 To cols) As String
For i=1 To cols
'the replace function used here removes spaces from the field definitions in the first row
fd(i) = xlsSheet.Cells( row, i ).Value
If Len(fd(i)) Then
fd(i)= Replace(fd(i), " ", “”)
'Remove the # in Home Phone #
fd(i)= Replace(fd(i), “#”, “”)
'Remove the / in Full/Part Time
fd(i)= Replace(fd(i), “/”, “”)
If IsNull(ArrayGetIndex(astrFields, fd(i))) Then
msg="The field name "& fd(i) &" does not appear in the form you have chosen."
If MsgBox(msg, MB_OKCANCEL + MB_ICONEXCLAMATION + MB_DEFBUTTON2) <> 1 Then
GoTo Done
End If
End If 'flag=1
End If
Next 'For i=1 To cols
Else 'row isn’t = 1
'Import each row into a new document
'Create a new doc
Set doc = db.CreateDocument
doc.Form = FormName
For i= 1 To cols
If Len(fd(i)) Then _
Set item = doc.ReplaceItemValue( fd(i), xlsSheet.Cells( row, i ).Value )
Next ’ i= 1 To cols
'Save the new doc
Call doc.Save( True, True )
End If 'Not row = 1 Then
'Print "Processing document number "& CStr(row) & " of " & CStr(rows)
Loop 'Do while true
Done:
On Error Resume Next 'protect against infinite error handing loops
'Print "Disconnecting from Excel..."
If Not xlsWorkbook Is Nothing Then
xlsWorkbook.Close False
End If ’ Not xlsWorkbook Is Nothing
If Not xlsApp Is Nothing Then
xlsApp.DisplayAlerts = False
xlsApp.Quit
Set xlsApp = Nothing
End If 'Not xlsApp Is Nothing
'Clear the status line
Exit Sub
ErrorHandler:
Select Case Err
Case 184
'MsgBox “No file chosen. Exiting Import.”
'Print “No file chosen. Exiting Import.”
Resume Done
Case 6
'MessageBox “Make sure that you do not have more than 65,536 rows of data to import.” ,MB_OK+MB_ICONINFORMATION,"Error! "
'Print “Too many rows in Excel document. Exiting Import. Disconnecting from Excel…”
Resume Done
Case Else
'MsgBox "Lotus Notes Error # " & Err & “. Please contact your Notes administrator for help. Exiting Import.”
'Print "Error # "& Err & " on line " & Erl & ": " & Error$
Resume Done
End Select
End Sub