Can't export calculated fields to Excel

  • Thread starter Thread starter Diane Yocom
  • Start date Start date
D

Diane Yocom

I have several reports that do summing in the group and report footer
sections. They work fine in Access, but when I try to Export them to Excel
97, I get an error message saying that the following error was detected, but
was repaired: "Renamed invalid sheet name". When I click OK, the
spreadsheet comes up blank.

I believe I've narrowed the problem down to the fact that I have a bunch of
calculated fields. To support that theory, I've also found reports that
mostly come through fine except for the title which is a calculated field
like: ="Priority Outreach Participants By Agency: " & UCase([SelAgency]).
In that case, the title comes through blank, but the rest of the report is
fine. and, I've got other reports with no calculated fields that also
export just fine.

If I export to RTF or HTML and then open in Excel, the data gets into Excel
fine, though the formatting is messed up. This is more work than my users
would like to do, so I'm hoping there's some way to get reports with
calculated fields to export properly. Any thoughts?

TIA,
Diane
 
One suggestion is to change the query that's associated with the report data
source so that the values are calculated in the query. For Example you might
do something like this:

SELECT [Tax] + [Freight] + [SubTotal] AS [InvoiceTotal], *
FROM tblInvoice;

You can embed functions in queries so if SubTotal is a calculated value you
can replace it with a user-defined function GetSubTotal() .
 
Have you tried changing the name (title)? Try dropping the colon and see if
that helps. Or if there are slashes in the SelAgency field try dropping those.
 
While I know that would work, it's not really an option for these reports.
Or, it would be if there was only one report, but there are a bunch with
this problem and I don't have the time to rewrite them.

It seems like you should be able to export the report just as it looks when
it's been loaded in Access, regardless of the underlying query.

Diane


Treebeard said:
One suggestion is to change the query that's associated with the report data
source so that the values are calculated in the query. For Example you might
do something like this:

SELECT [Tax] + [Freight] + [SubTotal] AS [InvoiceTotal], *
FROM tblInvoice;

You can embed functions in queries so if SubTotal is a calculated value you
can replace it with a user-defined function GetSubTotal() .


Diane Yocom said:
I have several reports that do summing in the group and report footer
sections. They work fine in Access, but when I try to Export them to Excel
97, I get an error message saying that the following error was detected, but
was repaired: "Renamed invalid sheet name". When I click OK, the
spreadsheet comes up blank.

I believe I've narrowed the problem down to the fact that I have a bunch of
calculated fields. To support that theory, I've also found reports that
mostly come through fine except for the title which is a calculated field
like: ="Priority Outreach Participants By Agency: " & UCase([SelAgency]).
In that case, the title comes through blank, but the rest of the report is
fine. and, I've got other reports with no calculated fields that also
export just fine.

If I export to RTF or HTML and then open in Excel, the data gets into Excel
fine, though the formatting is messed up. This is more work than my users
would like to do, so I'm hoping there's some way to get reports with
calculated fields to export properly. Any thoughts?

TIA,
Diane
Click to expand...
Click to expand...
 
I dropped the colon and that made no difference and there are no slashes in
SelAgency.

I did determine that this particular field would print out if it was moved
to a Group Header or to the Detail section, but it wouldn't print in the
Report Header, Page Header, or any footer sections.

But this field isn't really the biggest of my problems, I don't care much if
the title of the report doesn't export, but I have a bunch of calculated
fields in group footer sections (sums and counts) that just aren't showing
up and that's a problem!

Diane

John Spencer (MVP) said:
Have you tried changing the name (title)? Try dropping the colon and see if
that helps. Or if there are slashes in the SelAgency field try dropping those.



Diane said:
I have several reports that do summing in the group and report footer
sections. They work fine in Access, but when I try to Export them to Excel
97, I get an error message saying that the following error was detected, but
was repaired: "Renamed invalid sheet name". When I click OK, the
spreadsheet comes up blank.

I believe I've narrowed the problem down to the fact that I have a bunch of
calculated fields. To support that theory, I've also found reports that
mostly come through fine except for the title which is a calculated field
like: ="Priority Outreach Participants By Agency: " & UCase([SelAgency]).
In that case, the title comes through blank, but the rest of the report is
fine. and, I've got other reports with no calculated fields that also
export just fine.

If I export to RTF or HTML and then open in Excel, the data gets into Excel
fine, though the formatting is messed up. This is more work than my users
would like to do, so I'm hoping there's some way to get reports with
calculated fields to export properly. Any thoughts?

TIA,
Diane
Click to expand...
Click to expand...
 
Yeah, that is a problem. I don't know any fix to that, since when you export to
Excel, it seems to only export the Group headers and details sections. You
might try exporting to WORD and then copying the data from word over to excel.

I can't experiment right now, or I would try to come up with a better solution.

Diane said:
I dropped the colon and that made no difference and there are no slashes in
SelAgency.

I did determine that this particular field would print out if it was moved
to a Group Header or to the Detail section, but it wouldn't print in the
Report Header, Page Header, or any footer sections.

But this field isn't really the biggest of my problems, I don't care much if
the title of the report doesn't export, but I have a bunch of calculated
fields in group footer sections (sums and counts) that just aren't showing
up and that's a problem!

Diane

John Spencer (MVP) said:
Have you tried changing the name (title)? Try dropping the colon and see if
that helps. Or if there are slashes in the SelAgency field try dropping those.



Diane said:
I have several reports that do summing in the group and report footer
sections. They work fine in Access, but when I try to Export them to Excel
97, I get an error message saying that the following error was detected, but
was repaired: "Renamed invalid sheet name". When I click OK, the
spreadsheet comes up blank.

I believe I've narrowed the problem down to the fact that I have a bunch of
calculated fields. To support that theory, I've also found reports that
mostly come through fine except for the title which is a calculated field
like: ="Priority Outreach Participants By Agency: " & UCase([SelAgency]).
In that case, the title comes through blank, but the rest of the report is
fine. and, I've got other reports with no calculated fields that also
export just fine.

If I export to RTF or HTML and then open in Excel, the data gets into Excel
fine, though the formatting is messed up. This is more work than my users
would like to do, so I'm hoping there's some way to get reports with
calculated fields to export properly. Any thoughts?

TIA,
Diane
Click to expand...
Click to expand...
Click to expand...
 
Back
Top