Wednesday, 23 March 2011

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

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

'Import documents
endnow = False
Do While endnow = False

t=Trim(Cstr(Wsheet.Cells(row, 1).Value))
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))
Call doc.ComputeWithForm(False,False)
Call doc.Save(True,True)
Print cnt



Messagebox Cstr(cnt) + " documents imported"
Set App = Nothing
Set Wbook = Nothing
Set Wsheet = Nothing
End Sub

No comments:

Post a Comment