M
mjt7email-ggroups
Hello Everyone
I'm using Access 97.
I've used OutputTo everywhere to export data to Excel:
DoCmd.OutputTo acOutputReport, Screen.ActiveReport.Name,
acFormatXLS, , True
One of the tables has recently passed the 16,384 row limit for
OutputTo so it had to be replaced with TransferSpreadsheet. I used the
MVP FileSave API
(http://www.mvps.org/access/api/api0001.htm)
and a tiny bit of Excel Automation to make it similar to OutputTo. It
is working OK.
I decided to stop burying my head in the sand about Access 2007 not
being able to export a report and standardise by using
TransferSpreadsheet everywhere.
My problem is that one report uses format percent but it may not be a
number in Excel when using TransferSpreadsheet - description follows.
Possible solution 1.
In the Access query design, I can use the field property Format and
set it to "Percent" - when exported to Excel, the data is a number,
but loses the percent format eg 0.33333 or 0.6 etc.
Possible solution 2.
In Access, if I change the field in the query to CustomName: Format(x/
y), "Percent") the result in Excel is text data eg left aligned 33% or
60%. I'm rubbish at Excel and I couldn't even change it to a number!
Does anyone know how I can export the percent using
TransferSpreadsheet so that it is both percent *and* number in Excel
please?
Thank you for any help, regards
Marguerite
I'm using Access 97.
I've used OutputTo everywhere to export data to Excel:
DoCmd.OutputTo acOutputReport, Screen.ActiveReport.Name,
acFormatXLS, , True
One of the tables has recently passed the 16,384 row limit for
OutputTo so it had to be replaced with TransferSpreadsheet. I used the
MVP FileSave API
(http://www.mvps.org/access/api/api0001.htm)
and a tiny bit of Excel Automation to make it similar to OutputTo. It
is working OK.
I decided to stop burying my head in the sand about Access 2007 not
being able to export a report and standardise by using
TransferSpreadsheet everywhere.
My problem is that one report uses format percent but it may not be a
number in Excel when using TransferSpreadsheet - description follows.
Possible solution 1.
In the Access query design, I can use the field property Format and
set it to "Percent" - when exported to Excel, the data is a number,
but loses the percent format eg 0.33333 or 0.6 etc.
Possible solution 2.
In Access, if I change the field in the query to CustomName: Format(x/
y), "Percent") the result in Excel is text data eg left aligned 33% or
60%. I'm rubbish at Excel and I couldn't even change it to a number!
Does anyone know how I can export the percent using
TransferSpreadsheet so that it is both percent *and* number in Excel
please?
Thank you for any help, regards
Marguerite