How can you assign an entire Excel column to a variable inside VBA on Access?

Question

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.

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:

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:

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

EDIT 2: Here's how I defined my Excel Application and worksheet:

EDIT 3: Edited it to make it a little more complete in case it helps

EDIT 4: Updated more of the code

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)
. 
.
.

Answer 1

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])

Answer 2

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

You are getting that error because the Excel Range is not an Access Object but an Excel Object. You will have to define it as

Dim NameColumn As oXl.Range

Where oXL is the Excel Application that you have defined in your App.

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 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)