Using Excel as Database using VBA (Excel ADO)

VBA ADO Code for using Excel as Database


Though many database systems have come , still there is a need to use Excel as Backend database. The reasons might be many -- you get Excel sheets as a Report and do not want to import that into Access or SQL Server

Here is a simple code that will allow you to do exactly that

Sub Excel_ADO()

Dim cN As ADODB.Connection '* Connection String
Dim RS As ADODB.Recordset '* Record Set
Dim sQuery As String '* Query String
Dim i1 As Long
Dim lMaxRow As Long '* Last Row in the Sheet
Dim iRevCol As Integer '*
Dim i3 As Integer

On Error GoTo ADO_ERROR

Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
cN.ConnectionTimeout = 40
cN.Open

Set RS = New ADODB.Recordset

lMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iRevCol = 2

For i1 = 2 To lMaxRow

Application.StatusBar = i1
sQuery = "Select * From [Sheet1$]"

RS.ActiveConnection = cN
RS.Source = sQuery
RS.Open

If RS.EOF = True And RS.BOF = True Then
GoTo TakeNextRecord
End If

RS.MoveFirst
Do Until RS.EOF = True
sName = Trim$(RS("Name").Value)
sAge = Trim$(RS("Age").Value)
' Do some operations
RS.MoveNext
Loop


TakeNextRecord:
If RS.State <> adStateClosed Then
RS.Close
End If
Next i1

If Not RS Is Nothing Then Set RS = Nothing
If Not cN Is Nothing Then Set cN = Nothing

ADO_ERROR:
If Err <> 0 Then
Debug.Assert Err = 0
MsgBox Err.Description
Resume Next
End If

End Sub

All the code remains the same as Access ADO code except the change in connection string. cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"

See also:

Opening Comma Separate File (CSV) through ADO

Query Table with Excel as Data Source

Create Database with ADO / ADO Create Database

ADO connection string for Excel