excel - 从 Excel VBA 访问 Lotus Notes 数据库 - 如何选择 COLUMNVALUES？

Question

我正在研究将 Notes 数据库中的数据直接提取到 Excel 中，因为我们的财务人员正在手动重新输入数字。这是我到目前为止的代码:

子注释BB()

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

错误是“未设置对象变量或With Block变量”

令人烦恼的是，我可以在 ExcelVBA 调试窗口中看到我想要的值...所以我不能离得太远。我想它是如何正确访问一组项目

Answer 1

答案是声明一个变体数组并直接赋值...

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

此代码仅从 Notes View 中提取 12 个月(行)× 16 个部门(列)的账单值，并用它们填充 Excel 范围。当您知道(找出)如何时就很容易了!