Why ADO Instead of Opening the File

What ADO is, and where it runs

ADO (ActiveX Data Objects) is a Microsoft data layer that treats a data source - an Excel file, an Access database, SQL Server, a folder of CSVs - as something you send a SQL query to. The source is never opened in Excel; you get back only the rows and columns you asked for. Be clear about the platform first: ADO is Windows-only. There is no ADO and no ACE OLEDB provider in Excel for Mac, and a workbook built on it will not run there. On Windows it ships with Office.

The problem with Workbooks.Open

' The usual way: open, copy, close - and pay for every row
Dim src As Workbook

Set src = Workbooks.Open("C:\Data\Sales2026.xlsx", ReadOnly:=True)
src.Worksheets("Sales").Range("A1:D200000").Copy _
    Destination:=ThisWorkbook.Worksheets("Staging").Range("A1")
src.Close SaveChanges:=False

' ... and only now do you start filtering down to the 3 numbers you wanted

That works, and it is what most people write. It is also slow, it flickers, it fails when a colleague has the file locked, it fires whatever macros and volatile formulas live in that file, and it drags in 200,000 rows to answer a question about three. The cost grows in a straight line with the source.

Run this - it builds its own database

The macro below needs no server, no Access file and no download. It writes a small sales workbook into your temp folder, closes it, and then queries the closed file with SQL, grouping ten rows down to three before a single value reaches your sheet. Paste it into a standard module (Alt+F11, Insert - Module) and press F5 in QueryAClosedWorkbook.

Option Explicit

Private Const adOpenStatic As Long = 3
Private Const adLockReadOnly As Long = 1
Private Const adCmdText As Long = 1
Private Const adStateOpen As Long = 1

Sub QueryAClosedWorkbook()
    Dim sourcePath As String
    Dim connString As String
    Dim sql As String
    Dim conn As Object
    Dim rs As Object
    Dim target As Worksheet
    Dim i As Long

#If Mac Then
    MsgBox "ADO is a Windows-only technology. There is no ADO on Excel for Mac.", _
           vbExclamation
    Exit Sub
#End If

    ' 1 - build the source file, so this lesson needs nothing from you
    sourcePath = CreateDemoSource()

    ' 2 - somewhere to put the answer
    Set target = DemoSheet("AdoResult")
    target.Cells.Clear

    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                 "Data Source=" & sourcePath & ";" & _
                 "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

    sql = "SELECT [Region], COUNT(*) AS Orders, SUM([Amount]) AS Total " & _
          "FROM [Sales$] " & _
          "WHERE [Region] IS NOT NULL " & _
          "GROUP BY [Region] " & _
          "ORDER BY SUM([Amount]) DESC"

    ' 3 - ask the closed file a question
    On Error GoTo Failed

    Set conn = CreateObject("ADODB.Connection")
    conn.Open connString

    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, conn, adOpenStatic, adLockReadOnly, adCmdText

    For i = 0 To rs.Fields.Count - 1
        target.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    target.Range("A2").CopyFromRecordset rs

    target.Rows(1).Font.Bold = True
    target.Columns.AutoFit
    target.Activate

    MsgBox "3 rows out of 10, read from a file that was never opened:" & vbCrLf & _
           sourcePath, vbInformation

CleanUp:
    On Error Resume Next
    If Not rs Is Nothing Then
        If rs.State = adStateOpen Then rs.Close
    End If
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
    End If
    Set rs = Nothing
    Set conn = Nothing
    Exit Sub

Failed:
    MsgBox "ADO failed with error " & Err.Number & ":" & vbCrLf & Err.Description & _
           vbCrLf & vbCrLf & "If it says the provider cannot be found, the ACE OLEDB " & _
           "driver is missing or is the wrong bitness for your Office.", vbExclamation
    Resume CleanUp
End Sub

Private Function CreateDemoSource() As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rowsData As Variant
    Dim parts As Variant
    Dim r As Long
    Dim fullPath As String

    fullPath = Environ$("TEMP") & Application.PathSeparator & "NovaExcel_AdoDemo.xlsx"

    On Error Resume Next
    Kill fullPath
    On Error GoTo 0

    rowsData = Array( _
        "Region|Product|Amount", _
        "Zurich|Keyboard|89.90", _
        "Bern|Monitor|249.00", _
        "Zurich|Mouse|29.50", _
        "Basel|Keyboard|89.90", _
        "Bern|Docking station|179.00", _
        "Zurich|Monitor|249.00", _
        "Basel|Mouse|29.50", _
        "Bern|Keyboard|89.90", _
        "Zurich|Docking station|179.00", _
        "Basel|Monitor|249.00")

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set wb = Workbooks.Add(xlWBATWorksheet)
    Set ws = wb.Worksheets(1)
    ws.Name = "Sales"

    For r = LBound(rowsData) To UBound(rowsData)
        parts = Split(rowsData(r), "|")
        ws.Cells(r + 1, 1).Value = parts(0)
        ws.Cells(r + 1, 2).Value = parts(1)
        If r = LBound(rowsData) Then
            ws.Cells(r + 1, 3).Value = parts(2)
        Else
            ws.Cells(r + 1, 3).Value = Val(parts(2))
        End If
    Next r

    wb.SaveAs Filename:=fullPath, FileFormat:=51    ' 51 = .xlsx
    wb.Close SaveChanges:=False

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    CreateDemoSource = fullPath
End Function

Private Function DemoSheet(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = sheetName
    End If

    Set DemoSheet = ws
End Function

Reading it line by line

• The four Const lines exist because of late binding. CreateObject needs no library reference, so nothing breaks on a machine with another ADO version - but constants like adOpenStatic come from that reference, so you declare them.
• The connection string names the provider (ACE), the file, and HDR=YES, which tells the driver that row 1 holds column names rather than data.
• [Sales$] is how a worksheet is addressed here: sheet name, dollar sign, square brackets. Without brackets, a name with a space fails.
• The GROUP BY is the point of the exercise: the driver aggregates, so three rows cross into Excel, not ten - or not a million.
• CopyFromRecordset pours the result into the sheet in one statement. The For loop writes the field names separately, because it writes data only, never headers.
• CleanUp is reached on both paths. Success falls into it, the error handler jumps back with Resume CleanUp. An abandoned open connection leaves the source file locked.

The trap: a sheet is bigger than its data

[Sales$] does not mean "the rows with something in them". It means the sheet's used range, and that range does not shrink when somebody deletes rows - it only remembers how far the sheet was ever filled. Query a well-used export and you get your 12 rows followed by 800 rows of Null, a nonsense COUNT(*), and a read loop that dies on a Null. Nothing in the error points at the used range, which is why this costs an afternoon.

' Fragile: [Sales$] means "the used range", blank rows and all
sql = "SELECT [Region], [Amount] FROM [Sales$]"

' Safer: throw the empty rows away in the query
sql = "SELECT [Region], [Amount] FROM [Sales$] WHERE [Region] IS NOT NULL"

' Safest: state the rectangle yourself
sql = "SELECT [Region], [Amount] FROM [Sales$A1:C11]"

Where ADO is the wrong tool

ADO sees values only - formatting, charts and shapes are invisible to it - and merged cells or stacked headers confuse its guess at the column names. For a scheduled refresh of tidy data, Power Query is easier to maintain. ADO earns its place when you need a precise slice, on demand, from code.

How it goes on

The next lesson takes the connection string apart provider by provider, including the bitness mismatch that reports Provider cannot be found on a machine where the driver is plainly installed. Then come the dialect's quirks, cursors and Nulls, the four things CopyFromRecordset does not do, Access and SQL Server, and parameters instead of string concatenation.