Copy an Excel Range to an Array fast?

[timtaylor]

Hi guys,

is there a way to copy a range of excel cells to an array without looping through each cell.

the problem is that my software has to perform 10000 operations like this and the cellwise approach takes up to 30 minutes.
There should be a faster way.

Can you help me?

Thank you

 

[gvidali]

You may want to open the Excel worksheet as a standard ADO recordset: see the sample below... a snippet from a large class module we often use here...

Giorgio Vidali
Director of Software Development
Automated Business Designs, Inc.

Public Function RecordsetGet(FromExcelFileName As String, FromExcelDataRange As String) As ADODB.Recordset

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim exString As String
    Dim exRange As String
    Dim sql As String
    
    On Error GoTo L_LocalErrorHandler

    exString = "Data Source=" & FromExcelFileName & "; Extended Properties=Excel 8.0;"
    
    exRange = FromExcelDataRange
    If Right$(exRange, 1) <> &quot;$&quot; Then exRange = exRange & &quot;$&quot;

    Set cn = CreateObject(&quot;ADODB.Connection&quot;)
    Set rs = CreateObject(&quot;ADODB.Recordset&quot;)

    With cn
        .Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot;
        .ConnectionString = exString
        .CursorLocation = adUseClient
        .Open
    End With
        
    sql = &quot;SELECT * FROM [&quot; & exRange & &quot;]&quot;

    With rs
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = m_RecordsetCursorType
        .LockType = m_RecordsetLockType
        .Open sql
    End With

    Set RecordsetGet = rs

    Exit Function

L_LocalErrorHandler:

    Screen.MousePointer = vbDefault
    Err.Raise Err.Number, &quot;cExcelRecordset:RecordsetGet&quot;, Err.Description

End Function