从 Excel VBA 访问 Lotus Notes 数据库 - 如何获取 COLUMNVALUES？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28414967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Accessing Lotus Notes database from Excel VBA - how do I pick up COLUMNVALUES?
提问by Dave45
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:
我正在调查将数据从 Notes 数据库直接提取到 Excel 中，因为我们的财务人员正在手动重新输入数字 atm 这是我目前的代码：
Sub notesBB()
子注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
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
令人讨厌的是，我可以在 ExcelVBA 调试窗口中看到我想要的值……所以我离得不远了。我猜它如何正确访问一组项目
回答by Dave45
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 !
此代码仅从 Notes 视图中提取 12 个月（行）按 16 个部门（列）的账单值，并用它们填充 Excel 范围。当你知道（找出）如何时很容易！
