Reference Variables

[Forrest]

The following VBA code creates an Access ADODB recordset
and drops data into Excel. The last line does not work. If
I hard code fldCount to 3 and recCount to 8 it works. How
do I reference the variable to get to the bottom of the
records? My goal is to then run Excel functions on the
data set and return the results to Access.

Dim fldCount As Integer
Dim recCount As Long

'Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next

'Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
recCount = rst.RecordCount
xlWs.Cells(recCount, fldCount).Select

Thanks for any ideas

 

[Tushar Mehta]

See my response to the concurrent discussion in the same NG titled
"Dynamic range reference from vba"

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions