Accessing Lotus Notes database from Excel VBA - ho

Question

I am investigating pulling data from a Notes database directly into Excel as our Finance guy is manually re-typing figures a.t.m. This is my code so far:

Sub notesBB()

Const DATABASE = 1247
Dim r As Integer
Dim i As Integer
Dim c As Integer
Dim db As Object
Dim view As Object
Dim Entry As Object
Dim nav As Object
Dim Session As Object 'The notes session
Dim nam As Object
Dim val As Variant
Dim v As Double
Dim items As Object
Set Session = CreateObject("Lotus.NotesSession")
Call Session.Initialize
Set nam = Session.CreateName(Session.UserName)
user = nam.Common
Set db = Session.getdatabase("MSPreston", "Billbook1415.nsf")
Set view = db.GetView("By Month\By Dept")
view.AutoUpdate = False
Set nav = view.CreateViewNav
Set Entry = nav.GetFirst
val = Entry.childcount              
val = Entry.ColumnValues(6)         ' this doesn't work
Set items = Entry.ColumnValues      'from a suggestion on the net
val = items(6)                      'this doesn't work either
MsgBox (val)
End Sub

error is "object variable or With Block variable not set"

The annoying thing is that I can see the values I want in the ExcelVBA debug window... so I can't be far off. I guess its how to access an array of items properly

Answer 1

The answer is to declare a variant array and assign it directly...

Sub notesBB()
Const DATABASE = 1247
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 Session 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
Set Session = CreateObject("Lotus.NotesSession") 'Start a session to notes
Call Session.Initialize
Set nam = Session.CreateName(Session.UserName)
user = nam.Common
Set db = Session.getdatabase("MSPreston", "Billbook1415.nsf")
Set view = db.GetView("By Month\By Dept")
view.AutoUpdate = False
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

This code just extracts the 12 months (rows) by 16 departments (cols) bills values from the Notes view and populates an Excel range with them. Easy when you know (find out) how !