Export access 2010 form records to Excel

Does anyone have a way to export to excel only those records displayed on a filtered form in Access 2010 top Excel 2010?

Sandra
Sandra SmithRetiredAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
first create a query "qryExport"  with sql statement

select * from NameOfTable

use this code in the click event of a  button

Private Sub cmdExportToExcel_Click()
Dim sWhere As String, qd As DAO.QueryDef, nSql As String
If Me.Filter = "" Then
    sWhere = ""
    Else
    sWhere = Me.Filter
End If

Set qd = CurrentDb.QueryDefs("qryExport")
qd.sql = "select * from NameOfTable"
nSql = qd.sql & " Where " & sWhere
If Len(sWhere) > 0 Then
qd.sql = nSql
End If

DoCmd.TransferSpreadsheet acExport, 10, "qryExport", _
        "C:\ExportedToExcel.xlsx", True,


end sub
0
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try by creating a temporary table

Dim qrydef as QueryDef
DoCmd.DeleteObject acQuery, "qryTmp"
Set qrydef = CurrentDb.CreateQueryDef("qryTmp", Me.myForm.Form.RecordSource)
DoCmd.OutputTo acOutputQuery, "qryTmp", acFormatXLS, "C:\myXLFile.xls", True

Open in new window

Regards
0
 
Sandra SmithRetiredAuthor Commented:
They both worked at setting up the query and I needed the transfer methodology
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.