从Excel VBA访问Lotus Notes数据库 - 如何提取COLUMNVALUES？ [英] Accessing Lotus Notes database from Excel VBA - how do I pick up COLUMNVALUES?

问题描述

我正在调查从Notes数据库中将数据直接提取到Excel中，因为我们的财务人员手动重新输入数字a.t.m.
这是我的代码到目前为止：

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'注释会话
 Dim nam As Object 
 Dim val As Variant 
 Dim v As Double 
 Dim items As Object 
设置Session = CreateObject（Lotus.NotesSession）
调用Session.Initialize 
设置nam = Session.CreateName（Session.UserName）
 user = nam.Common 
设置db = Session.getdatabase（MSPreston，Billbook1415.nsf）
设置view = db.GetView（By Month\By Dept）
 view.AutoUpdate = False 
设置nav = view.CreateViewNav 
设置Entry = nav.GetFirst 
 val = Entry.childcount 
 val = Entry.ColumnValues（6）'这不' t work 
从网上的建议设置items = Entry.ColumnValues'
 val = items（6） '这不起作用
 MsgBox（val）
 End Sub 
  

错误是对象变量或块变量未设置

令人讨厌的事情是，我可以在ExcelVBA调试窗口中看到我想要的值...所以我可以不远处我想它如何正确地访问一系列的项目

解决方案

答案是声明一个变量数组并直接分配。 。

 子笔记BB（）
 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'注释会话
 Dim nam As Object'note用户名
 Dim v（）As Variant'保存小计值
点钞（12,16）'12个月，16个部门
r = 1 
工作表（1）.Range（A1：z99）。清除
设置Session = CreateObject（Lotus.NotesSession）'开始一个会话到笔记
调用Session.Initialize 
设置nam = Session.CreateName（Session.UserName）
 user = nam.Common 
设置db = Session.getdatabase（MSPreston，Billbook1415.nsf）
设置view = db.GetView （By Month\By Dept）
 view.AutoUpdate = False 
设置nav = vi ew.CreateViewNav 
设置条目= nav.GetFirst 
直到条目不是
如果Entry.isCategory然后
r = r + 1 
v = Entry.ColumnValues 
对于i = 1到16 
票据（v（0），i）= v（4 + i）
单元格（4 + r，2 + i）=票据（v（0），i ）
下一个
结束如果
设置条目= nav.getNextCategory（条目）
 DoEvents 
循环
结束Sub 
  

此代码仅从Notes视图中提取16个部门（cols）账单价值的12个月（行），并填充Excel范围。当你知道（找出）怎么样的时候很容易！

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

解决方案

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 !

这篇关于从Excel VBA访问Lotus Notes数据库 - 如何提取COLUMNVALUES？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！