Convert ADODB RecordSet to a 2 dimensional array

Question

I want to read an Excel file without openening the file and put the result in a two dim. array. For this I use the below code

Sub ReadExcelFileWithoutOpening()
Dim conn As Object
Dim rs As Object
Dim filePath As String
Dim sheetName As String
Dim query As String

filePath = "path to the excelfile" ' 
sheetName = "Sheet1" 

' Create the connection object
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' Connection string for Excel 2007 and later (.xlsx)
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & filePath & ";" & _
          "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

' SQL query to select all data from the sheet
query = "SELECT [Application],[Description],[Department],[NO FP NEW], [NO FP Changed], [NO FP DEL#], [Rapporting Period] As RecordCount FROM [" & sheetName & "$]"

rs.Open query, conn, adOpenStatic

 
Dim ID_Array() As String
Dim index As Integer: index = 0
Dim c As Integer: c = rs.RecordCount


' Loop through the records
Do Until rs.EOF
    If rs.Fields(3).Value <> "" Then
     ReDim Preserve ID_Array(index, 6)
        ID_Array(index, 0) = CStr(rs.Fields(0).Value)
        ID_Array(index, 1) = CStr(rs.Fields(1).Value)
        ID_Array(index, 2) = CStr(rs.Fields(2).Value)
           
        'Debug.Print rs.Fields(0).Value & "," & rs.Fields(1).Value & "," & rs.Fields(2).Value & "," & rs.Fields(3).Value & "," & rs.Fields(4).Value & "," & rs.Fields(5).Value & "," & rs.Fields(6).Value
        If IsNull(rs.Fields(0).Value) Then
            Exit Do:
        End If
        index = index + 1
    End If
    rs.MoveNext
Loop

' Clean up
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub

But I have some trouble with this code. Reading the Excel works, but rs.RecordCount always returns -1 eventhough I have set the connection to adOpenStatic

I would like to know the number of records I have to put into the array so I could create the array using

 ReDim ID_Array(NumberOfRecordCount, 6)

When I change my SQL query to

SELECT Count(*) AS RecordCount FROM [" & sheetName & "$]"

I am able to get the number of records in the dataset. But now I don't have the fields anymore. A combination like

SELECT [Application],[Description],[Department],[NO FP NEW], [NO FP Changed], [NO FP DEL#], [Rapporting Period], COUNT(*) As RecordCount FROM [" & sheetName & "$]"

Throws an exception when I execute the query. "The query doesn't contain the expression [fieldname] as part of the statitic function"

Redim Preserve on an array is only possible on the last dimension of the array. So I'm stuck here. I could execute two SQL queries. One resulting in the number of rows (Count(*)) and one result with the data needed, but I think that is not the way to go.

Why don't you just use GetRows to put the data in one shot into an array, so Dim vDat as Variant and vDat= rs.GetRows will give you the data in an array. — Storax
– Storax, Commented Sep 15 at 12:20
The reason why you get -1 is that you very likely do not use Option Explicit and that you did not add a reference to the ADODB library. This means that adOpenStaticwill be 0 instead of 3. And when opening a recordset with adOpenForwardOnly (look into the documentation!) you will not get the record count. — Storax
– Storax, Commented Sep 15 at 12:28
@Storax I have Option Explicit at the top of my module this function is part of and I don't know what you mean by a reference to the ADODB lib. CreateObject("ADODB.Recordset") is not enough? Or are you refering to Early Binding so I have to add a project reference? — Stephan
– Stephan, Commented Sep 15 at 12:44
If you really have Option Explicit at the top you should run into an Compiler error stating that adOpenStatic is undefined — Storax
– Storax, Commented Sep 15 at 12:47

Storax · Accepted Answer · 2025-09-15 12:49:46Z

1

This is a great example of why you should always use Option Explicit. Also, make sure to add all your references to the VBA project while you're coding.

If you skip Option Explicit and don’t reference the ADODB library, adOpenStatic will end up as an empty variant. So when you use it in rs.Open query, conn, adOpenStatic, it passes 0 instead of the right value, which is 3.

Apart from that you can get the data from the recordset much easier by just using GetRows liike that

Dim vDat as Variant
vDat = Rs.GetRows

Collectives™ on Stack Overflow

Convert ADODB RecordSet to a 2 dimensional array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related