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

Solution 1

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)

Solution 2

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

Comments

[KryptKeeper]

This is actually the part I'm having trouble with. I'm assuming the two NameFields aren't the same because the second corresponds specifically to the [Sheet1$a1:a10]/NameColumn, right? In any case, I can't get either of these to work. For the first one, what should the second NameField be? I just tried: AND NameField IN (Worksheet.Range("A1:A10")) because I couldn't think of what it would be but this gave me a "Type Mistmatch" error. Also, in your second example, NameColumn would be a subform so it's giving me errors because I think it's expecting a table or query.

[KryptKeeper]

Hi, thanks for the response! It didn't work exactly but I feel closer than I was before. My Excel Application that I defined is: "ApXL". After trying to do what you said (Dim NameColumn As ApXL.Range), I'm still getting the same error. However, if I try commenting out that code but still leave [Set NameColumn = Worksheet.Columns(1)] untouched, the error I get is: "Application-defined or object-defined error" which makes me think there's a different way to define the Range that would make this work. Any ideas?

[Siddharth Rout]

Can you share the code where you are defining the Excel Objects including the Worksheet. You can edit your question and paste the code there.

[Siddharth Rout]

Change Set NameColumn = Worksheet.Columns(1) to Set NameColumn = xlWSh.Columns(1) and change Dim NameColumn As oXl.Range to Dim NameColumn As Object as you are using LateBinding

[KryptKeeper]

Thanks, haha, but my code doesn't actually use Worksheet.Columns(1), it uses xlWSh.Columns(1), I just did that so it'd be more clear. I changed the definition of NameColumn to an Object, but it just gives me the "Application-defined or object-defined error". Also, I just wanted to mention I really appreciate your help :)

[Fionnuala]

I see from your edits that you are trying to work with an instance of Excel, that is not the way to go for queries. You can connect a range or a named range as a table and work with it as you would any Access table, more or less. You can also write a query that references a range in Excel. Does your outline query use Excel or Access?

[KryptKeeper]

I'm trying a completely different approach right now just to see if I can select the column as a whole by applying Worksheet Functions to it. For example, I'm trying to set a variable as: [WorksheetFunction.Match(xlWSh.Range("A3"), xlWSh.Columns(1), 0)] to see if I can get the right answer but I'm getting an "Object required" error. Any ideas why this would be?

[KryptKeeper]

Wow, I didn't know it was even possible to convert a range into a table. I'm going to look into that right now, but do you have any useful links that would be hard to find about doing something like that? And sorry, what do you mean by "outline query"? I'm doing most of my work in Access if that's what you mean.

[KryptKeeper]

I'm a little confused, which part did you change in the follow-up? It looks the same to me after going through it line by line. Out of curiosity how did you confirm that it worked? Maybe that's the part I messed up because I'm still getting the "Application-defined or object-defined error" message.

[Fionnuala]

You can link via Get External data on the menu or by TransferSpreadsheet.

[Siddharth Rout]

Ok paste the complete code in your question. I still see some piece missing :)

[Siddharth Rout]

@KryptKeeper: The code Set FirstColumn = xlWSh.Columns(1) looks absolutely ok to me. Is that what is creating the problem?

[KryptKeeper]

I believe that is it, but I'm not 100% sure since the pop-up box with the error appears on the form so it doesn't highlight which part of the code is incorrect.

[KryptKeeper]

My only concern here is that the document doesn't already exist when I'm writing the code. My form has a button that creates an excel spreadsheet, which the user can then save if they wish, but it isn't saved immediately so I'm not sure where I can point to for the .xls file location.

[Fionnuala]

If you are creating the spreadsheet, where is the data in the column come from? A recordset? If so, why not use that for NOT IN? Is there any reason why you should not save it to a temporary location, for safety if nothing else?