如何将整个 Excel 列分配给 Access 上 VBA 中的变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11415035/
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
How can you assign an entire Excel column to a variable inside VBA on Access?
提问by KryptKeeper
Currently I have a form on Access that filters records from a subform and creates an Excel document with the data on the first sheet upon clicking a button. I have a bunch of other calculations that occur on subsequent sheets but I need them to only occur for the entries where the "Name" field already exists in the "Name" column on the first sheet. The other calculations are done through an SQL statment.
目前，我在 Access 上有一个表单，它可以从子表单中过滤记录，并在单击按钮时使用第一个工作表上的数据创建一个 Excel 文档。我有一堆其他计算发生在后续工作表上，但我需要它们只发生在第一张工作表的“名称”列中已经存在“名称”字段的条目中。其他计算是通过 SQL 语句完成的。
My thought process is to do something like:
我的思考过程是做这样的事情：
SELECT (various fields) FROM (query name) WHERE (first couple of where statements) AND NameField IN NameColumn
Where NameField would be the name element and NameColumn would be the entire column. I've tried creating this variable in multiple ways without success. For instance one thing I tried was looping through the records adding a "(", ", " and ")" at the right instances but the string was too long and Access wouldn't run it. I also tried just defining the variable like:
其中 NameField 将是名称元素，而 NameColumn 将是整个列。我试过以多种方式创建这个变量，但没有成功。例如，我尝试过的一件事是遍历记录，在正确的实例中添加“(”、“、”和“)”，但字符串太长，Access 无法运行它。我也尝试过定义变量，如：
NameColumn = ExcelApplication.Range("A:A")
And
和
NameColumn = Worksheet.Range("A:A")
And
和
NameColumn = ExcelApplication.Columns("A:A").EntireColumn
etc, but all of these only seem to refer to the first entry of the column. I've tried researching solutions but I'm not finding anything useful either. Does anybody know how I would approach something like this?
等等，但所有这些似乎只指列的第一个条目。我试过研究解决方案，但我也没有发现任何有用的东西。有谁知道我会如何处理这样的事情？
EDIT: I should probably also mention that I thought I was only getting the first entry using these methods because NameColumn wasn't defined as anything before hand. But I tried doing:
编辑：我可能还应该提到我认为我只是使用这些方法获得了第一个条目，因为 NameColumn 没有事先定义为任何东西。但我尝试这样做：
Dim NameColumn As Range
but I would then get the following error:
但我会收到以下错误：
Compile Error:
User-defined type not defined
which is weird since after some research I've noticed Range seems to be a valid datatype
这很奇怪，因为经过一些研究，我注意到 Range 似乎是一个有效的数据类型
EDIT 2: Here's how I defined my Excel Application and worksheet:
编辑 2：这是我定义 Excel 应用程序和工作表的方式：
EDIT 3: Edited it to make it a little more complete in case it helps
编辑 3：编辑它以使其更完整，以防万一
EDIT 4: Updated more of the code
编辑 4：更新了更多代码
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
On Error GoTo err_handler
Set rst = frm.RecordsetClone
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True
Set xlWSh = xlWBk.Worksheets("Sheet1")
#THESE TWO BLOCKS COPY THE DATA FROM THE SUBFORM#
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
.
.
.
Dim FirstColumn As Object
Set FirstColumn = xlWSh.Columns(1)
.
.
.
回答by Siddharth Rout
but I would then get the following error: Compile Error: User-defined type not defined which is weird since after some research I've noticed Range seems to be a valid datatype
但我会得到以下错误：编译错误：未定义用户定义的类型这很奇怪，因为经过一些研究我注意到 Range 似乎是一个有效的数据类型
You are getting that error because the Excel Rangeis not an Access Object but an Excel Object. You will have to define it as
您收到该错误是因为 ExcelRange不是 Access 对象而是 Excel 对象。您必须将其定义为
Dim NameColumn As oXl.Range
Where oXL is the Excel Application that you have defined in your App.
其中 oXL 是您在应用程序中定义的 Excel 应用程序。
And to set the range, you cannot use
并且要设置范围，您不能使用
NameColumn = Worksheet.Range("A:A")
You have to use the word Set. So the above code changes to
你必须使用这个词Set。所以上面的代码改为
Set NameColumn = Worksheet.Range("A:A")
or
或者
Set NameColumn = Worksheet.Columns(1)
FOLLOWUP
跟进
I tried this and it works
我试过了，它有效
Dim ApXL As Object, xlWBk As Object, xlWSh As Object, NameColumn As Object
Set ApXL = CreateObject("Excel.Application")
ApXL.Visible = True
Set xlWBk = ApXL.Workbooks.Add
Set xlWSh = xlWBk.Worksheets("Sheet1")
Set NameColumn = xlWSh.Columns(1)
回答by Fionnuala
Why not use a subquery?
为什么不使用子查询？
AND NameField IN (SELECT NameField FROM [Sheet1$a1:a10])
Or for a named range:
或者对于命名范围：
AND NameField IN (SELECT NameField FROM NameColumn)
Re Comments
重新评论
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel8, _
"Atable", "z:\docs\test.xls", True, "A1:A15"
Finally:
最后：
SELECT * FROM Table1
WHERE ADate NOT IN (
SELECT SomeDate
FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test2.xls].[Sheet1$a1:a4])
