Saturday, May 10, 2008

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

12 comments:

  1. Hitesh1:44 AM

    This gives me an error as Sheet1$ is not a valid name. I also tried the Actual name of sheet, its still not working. Gives me the error as "Could not find object" Please suggest something. This can do a ton of good to me. :)

    ReplyDelete
    Replies
    1. Anonymous1:58 PM

      I am also getting this error. "The Microsoft Jet database engine could not find the object 'Sheet1$'.

      Delete
  2. Hitesh

    I tried to simulate your error, but couldn't. Can you post the name of the Sheet (does it have any special characters etc)

    ReplyDelete
  3. Using Excel as my database - how can I select specific records to move to MS word for mailing labels?

    ReplyDelete
  4. Anonymous2:12 AM

    i can't understand

    ReplyDelete
  5. Anonymous8:00 PM

    Try putting the square brackets around the sheet name, i.e [Sheet1$]

    ReplyDelete
  6. thank you it works by adding some reference library

    ReplyDelete
  7. Anonymous7:26 AM

    I have an spreadsheet that gets updated every day. My problem is that every time I open the spreadsheet, my queries return OLD information. I tried using cn.Properties.Refresh, but it still does not returned any updates, it still returns the same info as yesterdays. Am I missing something?

    ReplyDelete
  8. I Used square brackets for sheet name. but its not working

    ReplyDelete
  9. Thanks!

    Hint: Make sure you enable the ADODB Library

    1) go to VBE (ALT+F11 in excel)
    2) Go to Tools > References...
    3) Enable "Microsoft ActiveX Data Objects x.x Library"

    ReplyDelete
  10. Priya6:32 AM

    can u please tell me on how to conduct quiz using excel and track the time in which users have submitted the answer? so that we can find the winners easily. please help me

    ReplyDelete
  11. How to update or delete a recordset?

    this is my code
    open_connection -cal;ling a procedure
    strsql = "Delete from [View_Records$]"
    Conn.Execute strsql

    Updating or deletinng is not allpowed in this ISAM is what i get
    What to do? I had used read only false in the open connection also

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.