Import Excel file

Here is some sample code to import a Microsoft Excel file, rather than a CSV file.  I was looking for some sample code to help release the task on the server.

An important step is right at the end where you release the application task.  To do this we quit the Excel application and also set the Application, Workbook and Worksheet to Nothing.


Sub Initialize

Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim item As NotesItem
Dim App As Variant, Wbook As Variant, WSheet As Variant
Dim v As Variant
Dim row As Double
Dim  form As String, fileXL As String, calcf As String,  t As String
Dim title(255) As String, x(1) As String
Dim k As Integer, cols As Integer
Dim cnt As Long
Dim ExpDoc As NotesDocument
Dim ExpView As NotesView

Set db = session.CurrentDatabase
Set ExpView = db.GetView("luDuplicateExpenses")

Set App = CreateObject("Excel.Application")
App.Visible = False

'Choose Excel file
v=ws.OpenFileDialog(False,"Please Select Excel File", "*.xls", "")
If Isempty(v) Then Exit Sub
fileXL=v(0)
cnt=0

App.Workbooks.Open fileXL
Set Wbook = App.ActiveWorkbook
Set WSheet = Wbook.ActiveSheet

'Import documents
row=2
endnow = False
Do While endnow = False

t=Trim(Cstr(Wsheet.Cells(row, 1).Value))
If t="ENDOFFILE" Then
endnow=True
Goto exitdowhile
End If


If Trim(Cstr(Wsheet.Cells(row, 3).Value)) = "" Then
Goto exitdowhile
End If
'key = Trim(Cstr(Wsheet.Cells(row, 1).Value)) &_
'Trim(Cstr(Wsheet.Cells(row, 2).Value)) & _
'Trim(Cstr(Wsheet.Cells(row, 3).Value))
'Set ExpDoc = ExpView.GetDocumentByKey(key)

'If Not ExpDoc Is Nothing Then
' Goto exitdowhile
'End If

Set doc=db.CreateDocument
doc.Form = "Expense"
doc.UniqueID = Evaluate ("@Unique")
doc.ExpenseDate = Cdat(Trim(Cstr(Wsheet.Cells(row, 1).Value)))
doc.ExpenseCategory = "Mileage"
doc.ExpenseMiles = Cdbl(Trim(Cstr(Wsheet.Cells(row, 4).Value)))
doc.ExpenseValue = Cdbl(Trim(Cstr(Wsheet.Cells(row, 5).Value)))
doc.Notes = Trim(Cstr(Wsheet.Cells(row, 3).Value)) & "  -  " & Trim(Cstr(Wsheet.Cells(row, 4).Value))
'Calculate
Call doc.ComputeWithForm(False,False)
'Save
Call doc.Save(True,True)
cnt=cnt+1
Print cnt

exitdowhile:
row=row+1

Loop

fin:
Messagebox Cstr(cnt) + " documents imported"
App.Application.Quit
Set App = Nothing
Set Wbook = Nothing
Set Wsheet = Nothing
End Sub