I'm using the following to pull data from an Access database to Excel, however, rather than pulling it all to an empty sheet and then loading each cells value into my array, is it possible to pull data directly to the array?


Dim NSV(1 To 52) As Double
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim DBpath As String
Dim sSQL As String

Sheets("Data").Select
Cells.Select
Selection.ClearContents

DBpath = Range("Menu!G19").Value
Set cnt = New ADODB.Connection

#If VBA7 Then
cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBpath & ";"
#Else
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBpath & ";"
#End If

Set rst = New ADODB.Recordset

sSQL = "SELECT * FROM SalesValues;"

rst.Open sSQL, cnt
If Not rst.EOF Then
   For n = 1 To rst.Fields.Count
     ActiveSheet.Cells(1, n).Value = rst.Fields(n - 1).Name
   Next n
   Range("Data!A2").CopyFromRecordset rst
End If
rst.Close
cnt.close

For Sweep = 1 To 52
    NSV(Sweep) = Cells(2, Sweep + 1).Value
Next Sweep