Read from Access Table into Excel
Following is a very simple piece of code to import an Access Table into
Excel.
- Run the procedure ReadFromAccess.
- Copy the complete code given below:
Option Explicit 'Goto Menu - Tools->References and add reference to Microsoft ActiveX Data _ Objects 2.8 Library Dim objConnection As ADODB.Connection Sub ConnectToDatabase(strDBpath As String) Set objConnection = New ADODB.Connection objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=" & strDBpath End Sub Sub ReadFromAccess() Dim strDB As String Dim rstTable As ADODB.Recordset Dim strTable As String Dim intFields As Integer '---------------------User Inputs------------------------------- 'Provide database path strDB = "C:\Users\\Program Files\Root\Sample\db_samples.mdb" 'Provide SQL Query or Table name from database strTable = "Employee" '=============================================================== Call ConnectToDatabase(strDB) Set rstTable = New ADODB.Recordset rstTable.Open strTable, objConnection, adOpenKeyset, adLockOptimistic, adCmdTable With Sheet1 .UsedRange.Clear For intFields = 0 To rstTable.Fields.Count - 1 .Range("A1").Offset(, intFields).Value = rstTable.Fields(intFields).Name Next intFields .Range("A1").Resize(, rstTable.Fields.Count).Font.Bold = True .Range("A2").CopyFromRecordset rstTable End With Set rstTable = Nothing Call CloseDB End Sub Sub CloseDB() objConnection.Close Set objConnection = Nothing End Sub
»
- Vishesh's blog
- Login or register to post comments
- 20633 reads
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago