Can I import the results of an Access Query into Excel?

Can I import the results of an Access Query into Excel?

Current situation:
I have a fairly complex DAO Microsoft Access database which I maintain for my club, containing a few thousand records
I have to hand-off membership information on a monthly basis to a dozen committee members.
They do not use Access, but all use Excel.
I therefore use Access to build an Excel file, which I then email off to them ... but only after spending a lot of time cleaning it up and enhancing it.
There are 5 different spreadsheets which need to be produced.
.... and that entails an awful lot of work each month ... almost as much as maintaining the database!

Ideal situation:
I build an Excel spreadsheet with all of the VBA / Macros in it to do the enhancements etc ... (that shouldn't be a problem if I read the books and set my mind to it).

What I would like to know before embarking on this trail is whether it is possible, (within the Excel VBA/Macros), to do the following.

Open the Access database
run a query
pull the result of the query into the body of the Excel spreadsheet .... so that the Excel VBA can work on it.

I am using Office 2003 under Windows XP

Is this asking too much?

If it is possible, can you give me a clue as to the VBA code required, or at least, point me to a place where I can find such code examples.

Thank you for this insight.

I wanted to run this code from a command button on my worksheet .. so I have 'adjusted' it as below.

I have set up the DAO reference.

I have commented out the bits I don't think I need.

The code falls over at:- TargetRange = Range("a7")
with an error 91 Object variable not set.

Any clues would be gratefully received.





#################################################################
Private Sub CommandButton1_Click()
Call DAOCopyFromRecordSet
End Sub


Sub DAOCopyFromRecordSet()
' stolen from http://www.exceltip.com/st/Import_da...Excel/428.html
' Example: DAOCopyFromRecordSet "C:\FolderName\DataBaseName.mdb", _
"TableName", "FieldName", Range("C1")
Dim db As DAO.Database
Dim dbFullName As String
Dim TargetRange As Range
Dim rs As Recordset
dbFullName = "c:\a-pgr\pgr.mdb"
TargetRange = Range("a7")

Dim intColIndex As Integer
'Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(dbFullName)
Set rs = db.OpenRecordset("t2-members-details", dbOpenTable) ' all records
'Set rs = db.OpenRecordset("SELECT * FROM " & "t2-members-details" & _
" 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

Well ... that worked an absolute treat!
I feel quite ashamed for not noticing that.

I am very happy with this, but was wondering if it was possible to run an Access query and import the result... rather than importing a table.
If possible ... what would the code look like please?

OK.. I think I will change the select queries into MakeTables and take it from there.

thank you for all your help.
It is much appreciated