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
if I create a document manualy, @DbLookup is working properly. Could you please help me? thank you very much
Subject: @DbLookup formula not working in the view containing docs created by import from excel agent
Does the import form have any computed fields? Try opening on of the imported documents in a Notes client then press F9 to refresh and then save it. Does the lookup find the document? If so then just do a Doc.Computewithform before saving the document. I have seen this sort of behaviour several times.
Subject: @DbLookup formula not working in the view containing docs created by import from excel agent
If that is your exact code I would say that the problem is there is at least one typo e.g.
doc.From = “ImportForm”
should probably be
doc.Form=“Import Form”.
This also looks wrong:
xFilename = “E:\Import”+“.xls”
as there is a path, a file extension but no file name.
I would suggest that @dblookup is working fine. I would expect that you can’t see your imported documents in the view. They must be there for the lookup to work. Perhaps because the Form field is not set correctly.
Subject: RE: @DbLookup formula not working in the view containing docs created by import from excel agent
The ImportForm does not have any computed values.
I can see all the documents in the view.
I tried to open some, refresh them and save again but it didn’t work either.
AND NOW THIS IS STRANGE → I opened a document, went into edit mode, deleted one letter from a field, wrote exactly the same letter back and saved the documet. After saving, the document disappeared from the view but if I search for it, it will be found. Also, db lookup for that one document is working! Is this normal or what?!
@Command([RefreshSelectedDocuments]) → does this command exists? my designer does not recognize this command.
I use a @picklist formula get values from the view with my documents. it works fine, but @dblookup formula does not.
If I create a document manualy and do @dblookup with that document, everything is working fine. The problem must be in import.
Subject: @DbLookup formula not working in the view containing docs created by import from excel agent
Robert,
Allow me to suggest that if you want help with a dblookup, you actually post the dblookup you are doing. And if you go look at the view you are looking into, do the values from the imported documents actually show up there?
Subject: @DbLookup formula not working in the view containing docs created by import from excel agent
IIRC, @DBLookup does not play well with ComputeWithForm. The best way to deal with that is probably to create an agent which uses @Command([RefreshSelectedDocs]) and run that on the documents once they’ve been imported.
Subject: RE: @DbLookup formula not working in the view containing docs created by import from excel agent
Another interesting observation:
I open the view. I see all the documents that have been imported. Everything looks ok. But, if I search the view for a document that I see in front of my eyes, it does not find anything.
Now, I open the document that was not found by search but I can see it in the view. I go into edit mode, delete some value, or even a letter, write there the same letter again so it looks like I did nothing, than I save and close the document.
The document disapears from the view but if I do search in the view, the document is found. This is the weirdest thing I think I have seen so far.
Does anyone has experience with these aliens as well?
