OutputTo to Excel question

  • Thread starter Thread starter Shohoku79
  • Start date Start date
S

Shohoku79

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
--
 
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 datasheet view and check that the total is formatted the
way you want.
Export the query instead of the report.

Jeanette Cunningham
 
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
Datasheet 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 said:
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 datasheet view and check that the total is formatted the
way you want.
Export the query instead of the report.

Jeanette Cunningham

Shohoku79 said:
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
Click to expand...
Click to expand...
 
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 said:
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
Datasheet 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 said:
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 datasheet view and check that the total is formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

Shohoku79 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeanette:

If Macro is going to keep on giving the format error problem, I am going to
be using VBA code any way. So how do I go about Exporting a Query by VB?

After doing some researching, I found TransferSpreadsheet.... If I trigger
the event using the DoCmd.TransferSpreadsheet acExport within VB, it will
require me to enter a file path for the filename argument, I wanted to give
the user the ability to choose where he would want to put the generated file
and the file name, but I don't know how to bring up the window to do so.

Please let me know if there is a way.

Thank you again for your help

Shohoku
--

Jeanette Cunningham said:
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 said:
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
Datasheet 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 said:
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 datasheet view and check that the total is formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

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
Click to expand...
Click to expand...
Click to expand...
 
You can use the code at http://www.mvps.org/access/api/api0001.htm to let
the user choose the folder and filename in the normal way. Paste this code
(api0001) into a standard module in your database and save it with a name
like modFile

Use something like this in place of your macro
(the code below will open the save file dialog, user enters a file name and
where to save, clicks Ok and your query is exported)
----------------------------
Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If
---------------------------------

Note: replace qryExport with the name of your table or query
Jeanette Cunningham

Shohoku79 said:
Hi Jeanette:

If Macro is going to keep on giving the format error problem, I am going
to
be using VBA code any way. So how do I go about Exporting a Query by VB?

After doing some researching, I found TransferSpreadsheet.... If I trigger
the event using the DoCmd.TransferSpreadsheet acExport within VB, it will
require me to enter a file path for the filename argument, I wanted to
give
the user the ability to choose where he would want to put the generated
file
and the file name, but I don't know how to bring up the window to do so.

Please let me know if there is a way.

Thank you again for your help

Shohoku
--

Jeanette Cunningham said:
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 said:
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
Datasheet 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
--

:

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 datasheet view and check that the total is
formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

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
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeanette:

Thank you for the resources link. They were very helpful and I was able to
implement those into my database without much problems.

Here is another question though, after the Query has being exported to an
Excel file (of the user's choosing of file name and location) and opening up
the Excel file, it seems that all the headings as well as text data that were
transferred over have an apostrophe preceeding the cell contents and it only
shows up in the formula bar, not the sheet.
e.g. 'Joe Smith (formula bar) and Joe Smith (actual sheet).

This seem to be the way to indicate that this field should be treated as
text by Excel and won't really affect the way the sheet looks, but is there a
way to remove all the apostrophes in these cells as they are being exported
by Access?

Thank you,

Shohoku
--

Jeanette Cunningham said:
You can use the code at http://www.mvps.org/access/api/api0001.htm to let
the user choose the folder and filename in the normal way. Paste this code
(api0001) into a standard module in your database and save it with a name
like modFile

Use something like this in place of your macro
(the code below will open the save file dialog, user enters a file name and
where to save, clicks Ok and your query is exported)
----------------------------
Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If
---------------------------------

Note: replace qryExport with the name of your table or query
Jeanette Cunningham

Shohoku79 said:
Hi Jeanette:

If Macro is going to keep on giving the format error problem, I am going
to
be using VBA code any way. So how do I go about Exporting a Query by VB?

After doing some researching, I found TransferSpreadsheet.... If I trigger
the event using the DoCmd.TransferSpreadsheet acExport within VB, it will
require me to enter a file path for the filename argument, I wanted to
give
the user the ability to choose where he would want to put the generated
file
and the file name, but I don't know how to bring up the window to do so.

Please let me know if there is a way.

Thank you again for your help

Shohoku
--

Jeanette Cunningham said:
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

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
Datasheet 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
--

:

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 datasheet view and check that the total is
formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

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
Click to expand...
Click to expand...
Click to expand...
 
This is a very hot topic with those who export data to excel.
The apostrophe is added by Access during the export process.
As you note it forces excel to treat numbers as text.
If you change all the data to numbers in excel, you lose any leading zeros -
which is not usually a good thing.
If you change the error checking options in excel to turn off the checkbox
for showing errror, you can hide the apostrophes in later versions of excel.
The issue is an excel issue, if you ask on the excel discussion group, you
can get some help with this.

Jeanette Cunningham

Shohoku79 said:
Hi Jeanette:

Thank you for the resources link. They were very helpful and I was able to
implement those into my database without much problems.

Here is another question though, after the Query has being exported to an
Excel file (of the user's choosing of file name and location) and opening
up
the Excel file, it seems that all the headings as well as text data that
were
transferred over have an apostrophe preceeding the cell contents and it
only
shows up in the formula bar, not the sheet.
e.g. 'Joe Smith (formula bar) and Joe Smith (actual sheet).

This seem to be the way to indicate that this field should be treated as
text by Excel and won't really affect the way the sheet looks, but is
there a
way to remove all the apostrophes in these cells as they are being
exported
by Access?

Thank you,

Shohoku
--

Jeanette Cunningham said:
You can use the code at http://www.mvps.org/access/api/api0001.htm to let
the user choose the folder and filename in the normal way. Paste this
code
(api0001) into a standard module in your database and save it with a name
like modFile

Use something like this in place of your macro
(the code below will open the save file dialog, user enters a file name
and
where to save, clicks Ok and your query is exported)
----------------------------
Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If
---------------------------------

Note: replace qryExport with the name of your table or query
Jeanette Cunningham

Shohoku79 said:
Hi Jeanette:

If Macro is going to keep on giving the format error problem, I am
going
to
be using VBA code any way. So how do I go about Exporting a Query by
VB?

After doing some researching, I found TransferSpreadsheet.... If I
trigger
the event using the DoCmd.TransferSpreadsheet acExport within VB, it
will
require me to enter a file path for the filename argument, I wanted to
give
the user the ability to choose where he would want to put the generated
file
and the file name, but I don't know how to bring up the window to do
so.

Please let me know if there is a way.

Thank you again for your help

Shohoku
--

:

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

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
Datasheet 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
--

:

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 datasheet view and check that the total is
formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

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
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeanette:

I'll post my question in those areas, thank you so much for all the help you
have given me throughout this endeavor.
--


Jeanette Cunningham said:
This is a very hot topic with those who export data to excel.
The apostrophe is added by Access during the export process.
As you note it forces excel to treat numbers as text.
If you change all the data to numbers in excel, you lose any leading zeros -
which is not usually a good thing.
If you change the error checking options in excel to turn off the checkbox
for showing errror, you can hide the apostrophes in later versions of excel.
The issue is an excel issue, if you ask on the excel discussion group, you
can get some help with this.

Jeanette Cunningham

Shohoku79 said:
Hi Jeanette:

Thank you for the resources link. They were very helpful and I was able to
implement those into my database without much problems.

Here is another question though, after the Query has being exported to an
Excel file (of the user's choosing of file name and location) and opening
up
the Excel file, it seems that all the headings as well as text data that
were
transferred over have an apostrophe preceeding the cell contents and it
only
shows up in the formula bar, not the sheet.
e.g. 'Joe Smith (formula bar) and Joe Smith (actual sheet).

This seem to be the way to indicate that this field should be treated as
text by Excel and won't really affect the way the sheet looks, but is
there a
way to remove all the apostrophes in these cells as they are being
exported
by Access?

Thank you,

Shohoku
--

Jeanette Cunningham said:
You can use the code at http://www.mvps.org/access/api/api0001.htm to let
the user choose the folder and filename in the normal way. Paste this
code
(api0001) into a standard module in your database and save it with a name
like modFile

Use something like this in place of your macro
(the code below will open the save file dialog, user enters a file name
and
where to save, clicks Ok and your query is exported)
----------------------------
Dim strFilter As String
Dim strSaveFileName as string

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "qryExport", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If
---------------------------------

Note: replace qryExport with the name of your table or query
Jeanette Cunningham

Hi Jeanette:

If Macro is going to keep on giving the format error problem, I am
going
to
be using VBA code any way. So how do I go about Exporting a Query by
VB?

After doing some researching, I found TransferSpreadsheet.... If I
trigger
the event using the DoCmd.TransferSpreadsheet acExport within VB, it
will
require me to enter a file path for the filename argument, I wanted to
give
the user the ability to choose where he would want to put the generated
file
and the file name, but I don't know how to bring up the window to do
so.

Please let me know if there is a way.

Thank you again for your help

Shohoku
--

:

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

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
Datasheet 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
--

:

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 datasheet view and check that the total is
formatted
the
way you want.
Export the query instead of the report.

Jeanette Cunningham

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top