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”
VBA Tips & Tricks 2008 