Cannot export Access 2007 reports to Excel 2007

[nms]

I would really like to know whose bright idea it was to get rid of this
feature in Access 2007. In Access 2002 and Access 2003, I created so many
reports (Crosstab in particular with many many calculated fields that I could
not do in the query) and have a macro export to excel with all the calculated
fields and a parameter to pick the beginning date and the ending date from
the reports that is THEN linked to another Excel spreadsheet that creates
charts with automatic updates. Several of those spreadsheets have arrays
that create charts when the data changes each month to more or less items
depending on the month. So---does anyone know how do I do that now? We now
have to keep multiple machines with Office 2003 on them to be able to create
these reports monthly. Really, someone needs to think to ask if this was an
important feature or not BEFORE removing it...Does anyone else have this
problem?!

 

[Jeanette Cunningham]

nms,
the fact of microsoft losing that court case about exporting access reports
to excel is very frustrating for all those people who have reports that
export to excel in previous versions of access.
The method I use is to create a temp table to format the data how it is
needed to export to excel.
I use as many append queries and update formatting queries to massage the
data in the temp table until it is exactly right to export to excel.
Some exports require at least 3 different temp tables to get the correct
data into the temp table.

Be careful to empty each temp table at the start of the process that appends
data to the temp table, so you don't get any of the last export mixed in
with the new export data you are building.

Jeanette Cunnningham

 

[ftwguy]

I got caught by the lack of Excel export in Access 2007 reports when I
upgraded at the office. I called Microsift and at first was told "the loss
of function is something they were not aware of." They were suppose to call
back with a fix. I called again and was told "the Excel Export feature was
left off due to security reasons." I asked an explanation of what security
reasons, and I got no explanation.

I'm facing the same problem, but fortuately kept 1 PC with 2003 on it.
Problem is everyone has to run over to that work station now to do their
Report exports to Excel.

 

[ftwguy]

Jeanette:

What court case and why was that litigated as a feature from Access to Excel?

 

[Rick Brandt]

Jeanette:

What court case and why was that litigated as a feature from Access
to Excel?

The court case that MS lost had to do with being able to edit a linked Excel
sheet from Access. It is not related to the removal of the ability to
export reports to Excel. That was removed in 2007 "just because".

 

[Jeanette Cunningham]

Rick,
thanks for that authoritative answer.
However I have over time read several posts that stated it was due to a
court case and no one ever answered to say this was not true. Maybe we can
set the record straight at this point in time?

Jeanette Cunningham

 

[ftwguy]

Well it would sure be nice if they reinstate it via an update if they are
technically and legally able to. It was quite handy to use that export
feature from reports to Excel. We'll have to keep the 2003 version on at
least one PC as we can't do without that feature. MS Tech Support told me to
use XML Export and open it in Excel. That does not work. It does not
provide the REPORT data. It ONLY provides data from the Table in which the
report was created from.

 

[Jeanette Cunningham]

When you have the time, the work around is to set up a temp table.
For each report you want to export to excel, you set up the data in the temp
table.
Use update queries to massage the data to the appropriate fields and format
as needed for export to the excel spreadsheet.
Then export the contents of the temp table, or a query based on the temp
table to excel.
You could use TransferSpreadsheet or the CopyFromRecordset feature from
excel.

It is quite a bit of work to set up, but works very well.

Jeanette Cunningham

 

[Tony Toews [MVP]]

I would really like to know whose bright idea it was to get rid of this
feature in Access 2007. In Access 2002 and Access 2003, I created so many
reports (Crosstab in particular with many many calculated fields that I could
not do in the query) and have a macro export to excel with all the calculated
fields and a parameter to pick the beginning date and the ending date from
the reports that is THEN linked to another Excel spreadsheet that creates
charts with automatic updates. Several of those spreadsheets have arrays
that create charts when the data changes each month to more or less items
depending on the month. So---does anyone know how do I do that now?

There is some code at the www.mvps.org/access website which will
export a query to Excel. I'm offline right now so I can't give you
the exact URL.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jeanette Cunningham]

John,
thanks for the tip.

Jeanette Cunningham

First let me say that I am an absolute Access newbie

I ran a report and exported it to html, then opened the file with
Excel - seemed to work - YMMV

JC

 

[Deepti Jain]

Hi,

I have migrated the database from Access 2003 to Access 2007.
I am exporting the data from Access Report to the Excel 2007.I am getting the Error "Error 3270 Reserved Error" while using the command which is:-

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, QueryName, ReportFileName, True

It is prompting a widow while exporting the data and asking about the parameters for the report which is not having the records.If the report is empty then the error is coming otherwise the export is working fine.
Please advice.

Thanks & Regards,
Deepti Jain