CODEWITHSUNDEEP
vbalotus-noteslotus-dominolotus
Carol.Kar
Carol.Kar

Reputation: 5345

Connect to a specific lotus notes database/documen via vba

I can connect to lotus notes via the following code. So basically I connect to the database: CLASTNAME/O=TEST/C=US.nsf

Set oSession = CreateObject("Notes.NotesSession")
Server = oSession.GetEnvironmentString("MailServer", True)

UserName = oSession.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

'Open the mail database in notes
Set db = oSession.GETDATABASE("", MailDbName)

Set view = db.GetView("Customers") //ERROR

However, I want to connect to a specific database, which I have. Here is the document link: 

<NDL>
<REPLICA C1451C8A:00575D55>
<VIEW OD3B89A25B:7D1FR7SA-OM4923732F:011L111C>
<NOTE OFAAAA64WE:GH1Q0W0W-IUZ0987MNB:2222F4LÖ>
<HINT>CN=ZZZUSDMS09/O=ZZZ/C=US</HINT>
<REM>Database 'UserName', View 'Customers', Document 'AG: A list of all company customers, Jannuary 9, 2009'</REM>
</NDL>

This is what I tried:

Sub notesBB()
    'Const DATABASE = ""
    Dim r As Integer
    Dim i As Integer
    Dim db As Object
    Dim view As Object
    Dim Entry As Object
    Dim nav As Object
    Dim oSession As Object   'The notes session
    Dim nam As Object       ' notes username
    Dim v() As Variant      ' to hold the subtotal values
    Dim bills(12, 16)       ' 12 months, 16 departments
    r = 1
    Worksheets(1).Range("A1:Z99").Clear

    '##############################
    'Start a session to notes
    Set oSession = CreateObject("Notes.NotesSession")
    Server = oSession.GetEnvironmentString("MailServer", True)

    UserName = "CN=ZZZUSDMS09/O=ZZZ/C=US" 'oSession.UserName
    CustomerDbName = "CZZZUSDMS09/O=ZZZ/C=US" & ".nsf"
    'Open the mail database in notes
    Set db = oSession.GETDATABASE("", CustomerDbName)

    Set view = db.GetView("OD3B89A25B:7D1FR7SA-OM4923732F:011L111C")
    view.AutoUpdate = True // here I get an error
    Set nav = view.CreateViewNav 
    Set Entry = nav.GetFirst
    Do Until Entry Is Nothing
    If Entry.isCategory Then
        r = r + 1
        v = Entry.ColumnValues
        For i = 1 To 16
        bills(v(0), i) = v(4 + i)
        Cells(4 + r, 2 + i) = bills(v(0), i)
        Next
    End If
    Set Entry = nav.getNextCategory(Entry)
    DoEvents
    Loop
End Sub

However, as you can see I get an error hee: view.AutoUpdate = True // here I get an error

How to connect to this database via vba?

I really appreciate your answer!

Upvotes: 0

Views: 4365

Answers (3)

Dave45
Dave45

Reputation: 217

Nice to see my code getting recycled ! it seems to me that you aren't using the correct database and view names. Are they not Database 'UserName', and View 'Customers' ? (from your link). Whatever - when your XL VBA crashes out, in the Debug - Locals window, look for the object(s) that you have tried to instantiate with the SET command. If they show up as "nothing", you've got the SET command(s) wrong and trying to use the failed object (view) then crashes on the following line.

If your servername is "Yoda" I'd guess you need 

Set db = session.getdatabase("Yoda", "Username.nsf")
Set view = db.GetView("Customers")

Upvotes: 1

Richard Schwartz
Richard Schwartz

Reputation: 14628

Well, this doesn't look right:

CustomerDbName = "CZZZUSDMS09/O=ZZZ/C=US" & ".nsf"
'Open the mail database in notes
Set db = oSession.GETDATABASE("", CustomerDbName)

You're just appending ".nsf" to the user's fully disntinguished name in canonical form, and that would be an extremely unusual naming convention for databases on a server. Also, earlier in your code you retrieved the server name, but here you're specifying "" for the server name instead of using what you had retrieved, so the result is that the code will try to open the database on the local machine.

The NDL file is giving you the ReplicaID of the database here:

<REPLICA C1451C8A:00575D55>

You can use the OpenByReplicalID method instead:

repID = "C1451C8A00575D55"         ' note that the : is removed
'Open the database by replica id 
set db = new NotesDatabase("","")
If db.OpenByReplicaID( , "85255FA900747B84" ) Then
   Print( db.Title & " was successfully opened" )
Else
   Print( "Unable to open database" )
End If

The next problem, though, is that the NDL file is giving the view's UNID, not its name

<VIEW OD3B89A25B:7D1FR7SA-OM4923732F:011L111C>

There is no call in the Notes COM API that can retrieve a view by its UNID; you need the name for that. But do you really need to get the view? The NDL gives you the UNID of the document, here:

<NOTE OFAAAA64WE:GH1Q0W0W-IUZ0987MNB:2222F4LÖ>

So if your goal is to just get the specific document specified in the NDL, you can use a call to GetDocumentByUNID. Note, however that the actual UNID consists of only the 17 chars to the right of OF in the above tag, minus the : char. So your code would look like this:

unid = "AAAA64WEGH1Q0W0W" ' see note below!!
Set doc = db.GetDocumentByUnid(unid)

BTW, that UNID does not look legal. The chars should be hex, and the W, Q, and G are not. I'm going on the assumption that you (or someone) deliberately obfuscated the data in your NDL file.

If you do need to access the view, the NotesNoteCollection class may provide a way to get there, but it will not be trivial.

Finally, you may find this code from Stepehn Wissel helpful.

Upvotes: 2

Ken Pespisa
Ken Pespisa

Reputation: 22266

Specify the name of the view instead:

Set view = db.GetView("Customer")

Upvotes: 1

Related Questions