Export Access 2007 report to Excel as a text file

[Heather]

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.

 

[Ralph]

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 &
"myOutput.txt"

AccDb.CloseCurrentDatabase

Set AccRpt = Nothing
Set AccDb = Nothing