Hi,
i am writing a code to import files from excel into lotus. each row in the excel files will be occur as a new document in lotus. it can successfully import all the files from the excel.
But here is where the problem come out. The excel file i wanted to import will be updated monthly, so the data will keep changing as well. The code is able to import ALL the data but i need to filter it, I only want to add in new data from excel which is not exist in the lotus database. but after i add in some logic inside, it prompt out an error : wrong number of argument for automation object.
below is the code of the program…
Sub Click(Source As Button)
Dim session As New NotesSession
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 docs As NotesDocument
Dim view As NotesView
Dim dbValue As String
Dim exValue As String
Dim checked As Boolean
Dim crow As Long
On Error Goto ErrorHandler
Set db = session.CurrentDatabase
fn= uiws.Prompt(1, "Alert", "Make sure that the first row of your worksheet contains the EXACT Notes document field names from your form.")
'Get Excel file name
fn =uiws.OpenFileDialog(False, "Select the Excel File to Import", "Excel files | *.xls", "C:\Documents and Settings\Administrator\Desktop")
xlFilename = Cstr(fn(0)) ' This is the name of the Excel file that will be imported
'Get list of form names
x=0
Print "Preparing List of Database Forms ..."
Forall f In db.Forms
Redim Preserve formlist(x)
formlist(x)=f.name
x=x+1
Print "Preparing List of Database Forms ..."& Cstr(x)
End Forall
'Choose the form to use for import
formname = uiws.Prompt(4, "Choose Import Form", "Please select which form is to be used for this input.", formlist(0), formlist)
If formname= "" Then End
'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
Forall a In form.Aliases
formname=a
End Forall 'a In form.Aliases
End If 'Not Isempty(form.Aliases)
'Next we connect to Excel and open the file. Then start pulling over the records.
Print "Connecting to Excel..."
’ Create the excel object
Set xlsApp = CreateObject("Excel.Application")
'Open the file
Print "Opening the file : " & xlfilename
xlsApp.Workbooks.Open xlfilename
Set xlsWorkBook = xlsApp.ActiveWorkbook
Set xlsSheet = xlsWorkBook.ActiveSheet
xlsApp.Visible = False
xlsSheet.Cells.SpecialCells(11).Activate
rows = xlsApp.ActiveWindow.ActiveCell.Row
’ Number of rows to process
cols = xlsApp.ActiveWindow.ActiveCell.Column
’ Number of columns to process
’ replace status value as “inactive” if the file is discovered in database but not in excel
’ comparing eacn document in database with each row in excel before loops to the next document in database
Dim views As NotesView
Dim doc3 As NotesDocument
Dim ExValues As String
Dim DbValues As String
Dim ExRows As Long
Set views = db.GetView("Employee")
Set doc3 = views.GetFirstDocument
While Not (doc3 Is Nothing)
CheckLoops:
ExRows = 2
CheckLoop:
DbValues = doc3.Empname(0)
ExValues = xlsSheet.Cells(ExRows,2).Value
If (DbValues <> ExValues) Then
ExRows = ExRows +1
If (ExRows = rows +1)Then
Call doc3.ReplaceItemValue("Status","inactive")
Call doc3.Save(True,False)
Set doc3 = views.GetNextDocument(doc3)
Goto DoneLoop
End If
Goto CheckLoop
Else
Set doc3 = views.GetNextDocument(doc3)
Goto DoneLoop
End If
DoneLoop:
Wend
'Make sure we start at row 0
row = 0
Print "Starting import from Excel file..."
Set view = db.GetView("Employee")
Do While True
FrontLoop:
Set docs = view.GetFirstDocument
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
For i=1 To cols
Redim Preserve fd(i)
fd(i)=xlsSheet.Cells( row, i ).Value
flag=0
Forall f In form.Fields
If Lcase(fd(i)) = Lcase(f) Then flag=1
End Forall 'f In form.Fields
If flag=1 Then
Goto Skip
End If ' flag=1
Skip:
Next 'For i=1 To cols
End If 'row=1
'Import each row into a new document
If Not row = 1 Then
MiddleLoop:
exValue = xlsSheet.Cells(row,2).Value
dbValue = docs.Empname(0)
'replace status value to null if the file is available in both database and excel file
If(exValue = dbValue)Then
Call docs.ReplaceItemValue("Status"," ")
Call docs.Save( True, False )
Goto FrontLoop
End If
If(exValue<>dbValue)Then
Set docs = view.GetNextDocument(docs)
If(docs Is Nothing)Then Goto EndLoop
Goto MiddleLoop
End If
'Create a new doc
'and also change the format of the date into yyyy-mm-dd, IF it is identified as date
EndLoop:
Set doc = db.CreateDocument
doc.Form = FormName
For i= 1 To cols
If (Isdate(xlsSheet.Cells( row, i ).Value) = -1) Then
Set item = doc.ReplaceItemValue( fd(i), Cdat(Format(xlsSheet.Cells( row, i ).Value,"yyyy-mm-dd" )))
Call doc.ReplaceItemValue("Status","new")
Else
Set item = doc.ReplaceItemValue( fd(i), xlsSheet.Cells( row, i ).Value )
Call doc.ReplaceItemValue("Status","new")
End If
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:
Print "Disconnecting from Excel..."
'Close the Excel file without saving (we made no changes)
xlsWorkbook.Close False
'Close Excel
xlsApp.Quit
'Free the memory that we’d used
Set xlsApp = Nothing
'Clear the status line
Print " "
ErrorHandler:
If Err = 184 Then
Msgbox "No file chosen. Exiting Import."
Print "No file chosen. Exiting Import."
Resume ErrorOut
End If ' err=184
If Err = 6 Then
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..."
'Close the Excel file without saving(we made no changes)
xlsWorkbook.Close False
'Close Excel
xlsApp.Quit
'Free the memory that we’d used
Set xlsApp = Nothing
Resume ErrorOut
End If ' err=184
If (Err) And (Not Err = 184) And (Not Err = 6) Then
Msgbox "Lotus Notes Error # " & Err &". Please contact your Notes administrator for help. Exiting Import."
Print "Error # "& Err
If Not xlsWorkbook Is Nothing Then
xlsWorkbook.Close False
End If ' Not xlsWorkbook Is Nothing
If Not xlsApp Is Nothing Then
xlsApp.Quit False
End If 'Not xlsApp Is Nothing
Resume ErrorOut
End If '(Err) And (Not Err = 184) And (Not Err = 6)
ErrorOut:
' Msgbox "done"
End Sub