here is the cide that does import from excel:
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim view As NotesView
Dim row As Integer
Dim written As Integer
Dim xFilename As String
Set db = session.CurrentDatabase
Set doc = New NotesDocument(db)
Set doc = session.DocumentContext
doc.From = "ImportForm"
xFilename = "E:\Import"+".xls"
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
'new
Set Excel = CreateObject("Excel.application")
Excel.Visible = False
Set xlWorkbook = Excel.Workbooks.Open(xFileName)
Set xlWorkbook = Excel.ActiveWorkbook
Set xlsheet=xlWorkbook.ActiveSheet
'//Cycle through the rows of the Excel file, pulling the data over to Notes
Print "Starting import from Excel file..."
Dim NumberOfRows As Long
NumberOfRows = xlSheet.UsedRange.Rows.Count
row =0
written =0
row = row +2
Set doc = db.CreateDocument
Set doc = Nothing
For row = 2 To NumberOfRows
'If doc Is Nothing Then
With xlSheet
Set view = db.GetView("References")
Set doc = db.CreateDocument
doc.Form = "ImportForm"
Call doc.ComputeWithForm(False,False)
doc.RN = .Cells(row,1).Value
doc.Des = .Cells(row,2).Value
doc.T = .Cells(row,3).Value
doc.PC = .Cells(row,4).Value
doc.V =.Cells(row,5).Value
doc.BU = .Cells(row,9).Value
doc.SP = .Cells(row,12).Value
Call doc.save(True,True)
written = written + 1
row = row + 1
If row = NumberOfRows +1 Then
Goto Done
Else
End If
Print written & "records imported..."
End With
'End If
Next
Done:
Print "Disconecting from Excel..."
xlWorkBook.close False
Excel.quit
Set Excel = Nothing
Print " "
End Sub
Now, I have a view where I see all imported documents. Everythigs looks ok. But, If I use @Dblookup formula in a form that looks for values in this view, I will get an error - entry not found…
I tried to open one document in the view in the edit mode, typed anything in the empty hidden field, saved the document and the document dissapeared from the view. But than when I do @DbLookup and search for a value that was in that document, the value is found.
I wrote the problem with import probolem earlier but I didn’t get anywhere. Now I’m trying to create an agent that will cycle through the whole view, open each document in edit mode, enter whatever value in a hidden field (in this case the field is called test and the value is “test”, I 'm not sure of the purpose of this procedure but it’s the only way how to make it work)
So here is the agent, but the problem I’m having with it is, that it openes only the first document in the view. For the test purpose, I set up to open the document only 20 times. If I didn’t set this up, lotus runs out of memory and falls down.
Sub Initialize
Dim session As New NotesSession
Dim ws As New NotesUIWorkspace
Dim db As NotesDatabase
Dim view As NotesView
Dim varName As String
Set db = session.CurrentDatabase
Set view = db.GetView("References")
Set doc = view.GetFirstDocument
Dim i As Integer
i=0
'While Not(doc Is Nothing)
While i<20
Set nextdoc = view.GetNextDocument( doc )
Dim uidoc As NotesUIDocument
Call ws.EditDocument(True, doc)
doc.test="test"
Set uidoc = ws.CurrentDocument
Call doc.save(True, True)
'Call uidoc.Save
'Call uidoc.Close
'Set uidoc = Nothing
'Set doc = Nothing
Set Statdoc = nextdoc
i=i+1
Wend
End Sub
I am not very skilled with LotusScript yet. I was trying to search for some topics in this forum but didn’t find anything. Please, help me, this database is getting me crazy.
My questions are: Is it possible to fix the import code that there will be no agent needed.
If agent is needed, could you please help me to fix the code? The agent is suppose to cycle through the documents, open them and close. One after another. There are over 1000 documents so opening them all at once would kill the memory.
thank you very much
Robert Hakala