Export Access 2007 report to Excel as a text file



I have a complex Access report that I want to export to excel as a text file.
In Excel, I have recorded macros to import the text file and manipulate the
data, but I would like to run the text export and Excel code as part of the
Access code I use to compile the report. Is this possible?
The Export to Text button on the Print Preview ribbon exports the file
beautifully, but it would be less clumsy if it was a single operation.


Assuming you have a reference set to the Micorsoft Access 12.0 Object Library
in Excel.

Dim strDb As String
Dim strRpt As String
Dim strPath As String
Dim AccDb As New Access.Application
Dim AccRpt As AccessObject
strPath = ThisWorkbook.Path & "\"
strDb = strPath & "myAccessDatabaseName.accdb"
strRpt = "myRptName"
AccDb.OpenCurrentDatabase strDb
Set AccRpt = AccDb.CurrentProject.AllReports.Item(strRpt)

AccDb.DoCmd.OutputTo acOutputReport, strRpt, acFormatTXT, strPath &


Set AccRpt = Nothing
Set AccDb = Nothing

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question