Import Access 2007 report to Excel

[Guest]

I have a complex report with grouping, sub reports and subtotals that I would
like to export to an Excel Spreadsheet. Access has never been able to export
the report to Excel satisfactorily, and I always exported it as a text file
and then imported the text file to excel, using the text to columns wizard.
I don't mind losing the subtotals, but I want to retain the grouping and
columns in the report. Having to go through the import process every time
with the wizard is a pain, the columns of the report are standard but the
rows vary.

I used to be familiar with parsing files to Lotus 123 by inserting a line of
characters in the first row that identified characters as text, numeric etc
and identified when a new cell was to start. I have never done this in Excel
without a wizard, so have no idea what an Excel parse line looks like, if
such a creature exists.

Ideally I would like a command that runs the report, exports it to a text
file and then imports the text file to a spreadsheet. Alternatively if
someone could direct me to a numpty guide to importing data to excel without
using a wizard that would be nice.

 

[Guest]

No Problem - I recorded a Macro that solved it.

 

[Faisal...]

You can write a VB code (in Excel) with reference to the Microsoft
Access objects to access the data in your Access DB. Is your data on
your report from multiple tables?

Faisal...

 

[Faisal...]

You can write a VB code (in Excel) with reference to the Microsoft
Access objects to access the data in your Access DB. Is your data on
your report from multiple tables?

Faisal...

 

[Guest]

Yes, the report is from multiple tables and has six supreports, showing the
data for each project up to the current year, the total for the cusrrent year
and subtotals for each group of data. I exported the report to a text file
and recorded a macro that imports the text file and strips out the blank
lines. From this point I can work on the file. Do you think this excel
routine could be attached to the Access code that runs the report?

 

[Faisal...]

If you want to run it from access then use the following vb codes
(update appropriately) to export to a text file:

DoCmd.OutputTo acOutputReport, "REPORT NAME", "Text Files (*.txt)", "c:
\YOURFOLDER\OUTPUT.txt"

If you want to run it further as to import it to your spreadsheet,
then include reference to Microsoft Excel Object (Tool>Reference)

The add something like this:
Dim Excel_File As Excel.Workbook
Set Excel_File = Excel.Workbooks.Open("c:\YOURFOLDER\OUTPUT.txt")

Then you can add excel_vb. Example:
MsgBox (Excel_File.Sheets(1).Cells(5, 2))