Re: OutputTo to Excel question

I can't help you with the macro error message as I don't use macros.
I wouldn't expect that error message if the export was done using VBA code
instead of a macro.
One disadvantage of macros is the lack of error handling.

Jeanette Cunningham

"Shohoku79" <Shohoku79@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:750AEA33-32B0-4A30-9244-7A0A347FC6B9@xxxxxxxxxxxxxxxx
Hi Jeanette:

Thank you for your response, I tried your suggestion and attempted to grab
the result from a query. Except my transactions table does not store the
sums
at all, all the end results (as printed on Cks/Reports) were done by
summing
up "Individual Amounts" or transactions associated with a CK#. Hence the
query for this field is written as
Field:Total: Individual Amount
Table: Transactions
Total: Sum

However, once I trigger the Macro to OutputTo the Query into Excel, it
gives
me the error message: "File Error. Some Number Formats may have been
lost."
and I can click OK to continue. The result looks like it does transferred
over the $ sign and all. However, it looks like the format for this column
in
Excel is "Custom."

Now, if I were to run the Query by itself, the output will be shown in
Data*** view. If I use the File->Export and choose to Export that Query
as
Excel, the result will not display the file error message, but the numbers
displayed will be in "Currency" as it should be. This would be the same if
I
were to right click on Query and choose Export from the default database
menu.

So it looks like if I were to use Macro/OutputTo, I'd get the format error
message, but if I manually Export the Query, I won't.

I would like to click a button to automate the process of Exporting the
Query into Excel format (autostart enabled if possible), so it looks like
it
will involve VBscripting in the backend. How should I go about approaching
that?

Thanks again,

Shohoku
--

"Jeanette Cunningham" wrote:

Shohoku,

Save a query that pulls the same data as the report.
In the query add an extra column that formats the Total as currency.

In this column put
TotalAmount:CCur([TotalFieldName])
Uncheck the show checkbox for the original Total column
Switch the query to data*** view and check that the total is formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

"Shohoku79" <Shohoku79@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FAB7C555-9967-4103-9DF7-7015C95C9B42@xxxxxxxxxxxxxxxx
Hello:

I guess this question is a combination of Exporting
data/Reports/Macros....

I have used Access to write a Check Printing application. I now have a
report where the user will enter a set of date ranges and the report
will
pull all the checks wrote within that date range. It will also combine
the
individual transactions under each check and provide the total amount
for
the
checks using Sum([Individual Amounts]) which will workout the
pluses/minuses.

The report view works good and displays the data correctly. I also
allowed
the user to be able to output the report data into an Excel file. I
used a
button to trigger a Macro that runs OutputTo and I chose the Report as
the
object and MS Excel as the object type and let the user select the file
name.

One thing puzzles me is in the output Excel file. The total amount for
the
Check pulled is supposed to be in Currency format, and shows up that
way
in
the report with $ sign and all. However, in the Excel file generated
the
column that had the totals are all showing General Numbers format. I've
gone
back and looked at my reports/tables designs and this field is listed
as
Currency. But for some reason, the total that appeared correctly in the
report view didn't get its data format exported into Excel.

What could I be missing?

Thanks in advance.

Shohoku79
--






.