Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Getting Data from Access Table from Excel Macro


  • Please log in to reply

#1
TomPainter

TomPainter

    New Member

  • Member
  • Pip
  • 1 posts
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
  • 0

Advertisements


#2
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
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
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP