title: How can you assign an entire Excel column to a variable inside VBA on Access?
body: <p>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.</p>
  
  <p>My thought process is to do something like: </p>
  
  <pre><code>SELECT (various fields) FROM (query name) WHERE (first couple of where statements) AND NameField IN NameColumn
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>NameColumn = ExcelApplication.Range("A:A")
  </code></pre>
  
  <p>And</p>
  
  <pre><code>NameColumn = Worksheet.Range("A:A")
  </code></pre>
  
  <p>And</p>
  
  <pre><code>NameColumn = ExcelApplication.Columns("A:A").EntireColumn
  </code></pre>
  
  <p>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?</p>
  
  <p>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:</p>
  
  <pre><code>Dim NameColumn As Range
  </code></pre>
  
  <p>but I would then get the following error:</p>
  
  <pre><code>Compile Error:
  User-defined type not defined
  </code></pre>
  
  <p>which is weird since after some research I've noticed Range seems to be a valid datatype</p>
  
  <p>EDIT 2: Here's how I defined my Excel Application and worksheet:</p>
  
  <p>EDIT 3: Edited it to make it a little more complete in case it helps</p>
  
  <p>EDIT 4: Updated more of the code</p>
  
  <pre><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)
  . 
  .
  .
  </code></pre>