How can I copy an excel worksheet from a closed XLS file into a string array using VBA/ADO?

Question

I need to quickly and efficiently get some data out of a LOT of xls files. Opening new instances of excel for each is slow and causes all sorts of problem. I have seen some basic answers on how to open XLS files using ADO.

I would like a proper functions with a file name and worksheet name as input, and a string array as output.

Something that can be called from the immediate windows, here is an example/

Example for a sheet like this

In immediate window:

print XLStoArray("C:\test.xls","Sheet1$")(1,1)
hello, test !

Answer 1

This is my best answer so far, it might not be the fastest/cleanest way possible but it works !

'In VBA IDE, go to Tools -> References... and add "Microsoft ActiveX Data Objects 2.1 Library"  (may work with other versions)
Function ADOXLStoArray(FilePath As String, SheetName As String, Optional ColumnLimit As Long) As String()
    
    If FileExists(FilePath) = False Then Debug.Print "file does not exist": Exit Function
    
    Dim Connection As ADODB.Connection, RecordSet As ADODB.RecordSet, myArray() As String
    Set Connection = ADOGetDBConnection(FilePath)
    Set RecordSet = ADOGetRecordSet(Connection, SheetName)
    ReDim myArray(RecordSet.RecordCount, RecordSet.Fields.Count - 1)
    
    Dim x As Long, y As Long
    
    For y = 0 To RecordSet.Fields.Count - 1
        myArray(x, y) = IIf(IsNull(RecordSet.Fields(y).Name), "", RecordSet.Fields(y).Name)
    Next y
    
    x = x + 1
    
    While (Not RecordSet.EOF)
        For y = 0 To RecordSet.Fields.Count - 1
            myArray(x, y) = IIf(IsNull(RecordSet.Fields(y).Value), "", RecordSet.Fields(y).Value)
        Next y
        x = x + 1
        RecordSet.MoveNext
    Wend

    ADOXLStoArray = myArray
End Function

Function ADOGetDBConnection(FilePath As String) As ADODB.Connection
    Set ADOGetDBConnection = New ADODB.Connection
    ADOGetDBConnection.Provider = "Microsoft.ACE.OLEDB.12.0"
    ADOGetDBConnection.ConnectionString = "Data Source=" & FilePath & ";Extended Properties = 'Excel 12.0 Xml;HDR =YES'"
    ADOGetDBConnection.Open
End Function

Function ADOGetRecordSet(Connection As ADODB.Connection, SheetName As String) As ADODB.RecordSet
    Dim myRecordSet As New ADODB.RecordSet
    myRecordSet.CursorType = adUseClient
    myRecordSet.Open "Select * FROM [" & SheetName & "]", Connection
    Set ADOGetRecordSet = myRecordSet
End Function