[TomPainter]

I am trying to read data from an Access Table from an Excel Macro? Can anyone give me an example to use?

Much appreciated,

- Tom

[Advertisements]

Tom,
I found and tested the following code to import data from Access97 to Excel2003

TIP - it didn't work at first and I had to add a reference to a library.
In the Visual Basic for Applications editor screen perform the following steps;
From the dropdown menu > select - Tools - References
Scroll down until you find "Microsoft DAO 2.5/3.5 Compatibility Library"
Select this item and ensure a check mark appears in the box
Click OK

If you repeat these steps you will see the selected libraries are grouped at the top of the list.


The Code
Sub DAOCopyFromAccessToExcel()

'http://www.exceltip....s_to_Excel_(DAO)_using_VBA_in_Microsoft_Excel/428.html

'the following example could be used to parse values
'Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, FieldName As String, TargetRange As Range)
'where data values would look like >> DAOCopyFromRecordSet("C:\FolderName\DataBaseName.mdb", "TableName", "FieldName", Range("C1"))
'http://www.exceltip....s_to_Excel_(DAO)_using_VBA_in_Microsoft_Excel/428.html

Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim TargetRange As Range

Dim db As database
Dim rs As Recordset
Dim intColIndex As Integer

DBFullName = "C:\Documents and Settings\Owner\My Documents\Technical\TESTING\MsAcc97_Testing.mdb"
TableName = "tblFruit"
FieldName = "Fruit"
Set TargetRange = Range("A1")

Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records

'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
" WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records


' write field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next

' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs

Set rs = Nothing
db.Close
Set db = Nothing

End Sub



Hope this Helps
DSM

[dsm]

Tom,
I found and tested the following code to import data from Access97 to Excel2003

TIP - it didn't work at first and I had to add a reference to a library.
In the Visual Basic for Applications editor screen perform the following steps;
From the dropdown menu > select - Tools - References
Scroll down until you find "Microsoft DAO 2.5/3.5 Compatibility Library"
Select this item and ensure a check mark appears in the box
Click OK

If you repeat these steps you will see the selected libraries are grouped at the top of the list.


The Code
Sub DAOCopyFromAccessToExcel()

'http://www.exceltip....s_to_Excel_(DAO)_using_VBA_in_Microsoft_Excel/428.html

'the following example could be used to parse values
'Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, FieldName As String, TargetRange As Range)
'where data values would look like >> DAOCopyFromRecordSet("C:\FolderName\DataBaseName.mdb", "TableName", "FieldName", Range("C1"))
'http://www.exceltip....s_to_Excel_(DAO)_using_VBA_in_Microsoft_Excel/428.html

Dim DBFullName As String
Dim TableName As String
Dim FieldName As String
Dim TargetRange As Range

Dim db As database
Dim rs As Recordset
Dim intColIndex As Integer

DBFullName = "C:\Documents and Settings\Owner\My Documents\Technical\TESTING\MsAcc97_Testing.mdb"
TableName = "tblFruit"
FieldName = "Fruit"
Set TargetRange = Range("A1")

Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records

'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
" WHERE " & FieldName & _
" = 'MyCriteria'", dbReadOnly) ' filter records


' write field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next

' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs

Set rs = Nothing
db.Close
Set db = Nothing

End Sub



Hope this Helps
DSM