Yes, here is some code I use to do the same thing. If this isn't enough help
post a follow up question. In a query (in my case called "qryBoatInfo") you
put the criteria to be Selected_Model() or what ever you name this function.
I took out a lot of stuff so look it over for the basics.
Option Compare Database
Option Explicit
Public strPath As String
Public vModel As String
Public Sub Boat_Inquiry()
On Error GoTo Err_Boat_Inquiry
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim xlsApp As Object
Dim wkbTemp As Object
Dim R As Long
Set db = CurrentDb()
On Error Resume Next
Set xlsApp = GetObject("Excel.application")
If Err Then
Set xlsApp = CreateObject("Excel.Application")
End If
xlsApp.Visible = False
xlsApp.Activate
db.Execute "DELETE tblModels.Model FROM tblModels;"
db.Execute "INSERT INTO tblModels ( Model ) SELECT tblBoatInfo.Model " & _
"FROM tblBoatInfo GROUP BY tblBoatInfo.Model;"
If Len(Dir("C:\My Documents", vbDirectory)) = 0 Then
' Directory doesn't exist. Create It
MkDir ("C:\My Documents")
End If
strPath = "C:\My Documents\Boats.xls"
If Len(Dir(strPath)) > 0 Then
Kill strPath
End If
Set rs = db.OpenRecordset("tblModels")
R = 0
Do While Not .EOF
vModel = !Model
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryBoatInfo", strPath, , vModel
.MoveNext
R = R + 1
Loop
end sub
Function Selected_Model()
Selected_Model = vModel
End Function
