CODEWITHSUNDEEP
ms-accessvba
Philo
Philo

Reputation: 45

Is there a way to export a filtered form to Excel in MS Access?

I have a Continuous form within a Navigation Form in access. I have set the form to allow "data entry" in the property sheet settings under the Data tab. With this setting the form loads to a new data entry record. The problem is with the "Allow to Data entry" set to yes, when I try export to Excel, it always exports a sheet with empty cells, it only shows the fields for the search criteria but no data.

I set the "Allow data entry to no" and when I did that the export to Excel button worked and exported data to an Excel sheet.

ExportWithFormatting
Object Type: Form
OutputFormat: Excel Workbook xlsx
OUtputFile: Blank
Auto Start: Yes
Template File:
Encoding: OUtput
Quality: Print

I would like for the export to Excel button to export data to Excel based on the search criteria I have in my form.

Upvotes: 0

Views: 3387

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 48964

It not clear why you using data enter = yes, as that means the form will ONLY show new records you are entering. So, that idea is likely a bad idea.

However, assuming the form displays what you want after filtering?

Then something like this should work.

Place a button on the form (say up in the heading).

Private Sub Command47_Click()

  Dim strSQL     As String

  strSQL = Me.RecordSource

  strSQL = strSQL & " WHERE " & Me.Filter

  With CurrentDb.QueryDefs("qryExport")
     .SQL = strSQL
  End With

  ' now export the query  with critera to excel

  Dim strOutFile    As String

  strOutFile = "c:\ExcelData\Test.xlsx"

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
        "qryExport", strOutFile, True
end Sub

The query used in above is assumed to be a query on the table, and can be quite much any legal sql query - since our code OVERWRITES the query and sql each time, and then we export that query.

Upvotes: 1

Related Questions