acFormat XLS

I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

I don't have anything to try this with, but you might try leaving the Format
argument blank in your code. Access should then prompt you. Hopefully,
you'll be able to show it what type of file.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Rose B" wrote in message
...
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to
Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

Hi Rose,

The ability to export a report to Excel gets more limited in new
versions of Access. So you might want to contemplate changing what you are
doing as a means of preparation for the future. Maybe use the DoCmd.
TransferSpreadsheet command instead to transfer the data from the report's
query into Excel.

Clifford Bass

Rose B wrote:
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

--
Message posted via http://www.accessmonster.com

"Rose B" wrote in message
...
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to
Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

If your code is using:

DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, "c:\1.xls"
You could Try

"Excel 97 - Excel 2003 Workbook (*.xls)"
so:

DoCmd.OutputTo acOutputReport, strReport, "Excel 97 - Excel 2003 Workbook
(*.xls)", "c:\1.xls"
The above works in a2010 beta, but see what happnes in 2007. My VPC version
of access 2007 seems to not work for either of the above, so perhaps it will
work for you...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Thanks Albert - that works!

"Albert D. Kallal" wrote:


"Rose B" wrote in message
...
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to
Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

If your code is using:

DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, "c:\1.xls"
You could Try

"Excel 97 - Excel 2003 Workbook (*.xls)"
so:

DoCmd.OutputTo acOutputReport, strReport, "Excel 97 - Excel 2003 Workbook
(*.xls)", "c:\1.xls"
The above works in a2010 beta, but see what happnes in 2007. My VPC version
of access 2007 seems to not work for either of the above, so perhaps it will
work for you...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada




.

Thanks Arvin, this worked! The response from Albert worked too - and has the
advantage of not asking for the file type (I have different buttons to choose
if the user wishes to export to Word, PDF etc.) but this gives me some
flexibility.

"Arvin Meyer [MVP]" wrote:

I don't have anything to try this with, but you might try leaving the Format
argument blank in your code. Access should then prompt you. Hopefully,
you'll be able to show it what type of file.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Rose B" wrote in message
...
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to
Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?


.

Thanks Clifford. I had played around with the TransferSpreadsheet but in this
particular instance this solution isn't so good for me as the report is also
having filters set depending upon user choices. I will remember this in
future though.

"Clifford Bass via AccessMonster.com" wrote:

Hi Rose,

The ability to export a report to Excel gets more limited in new
versions of Access. So you might want to contemplate changing what you are
doing as a means of preparation for the future. Maybe use the DoCmd.
TransferSpreadsheet command instead to transfer the data from the report's
query into Excel.

Clifford Bass

Rose B wrote:
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

--
Message posted via http://www.accessmonster.com

.

"Rose B" wrote in message
...
Thanks Arvin, this worked! The response from Albert worked too - and has
the
advantage of not asking for the file type (I have different buttons to
choose
if the user wishes to export to Word, PDF etc.) but this gives me some
flexibility.

Great! Remember that each version will never know about future versions, and
historically, Microsoft codes conservatively to connect to as much data as
possible. One cannot guarantee, however, that these solutions will work in
the next version. That said, they usually do, and generally only require a
minor update if they don't.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi Rose,

You are welcome!

Clifford Bass

Rose B wrote:
Thanks Clifford. I had played around with the TransferSpreadsheet but in this
particular instance this solution isn't so good for me as the report is also
having filters set depending upon user choices. I will remember this in
future though.

Hi Rose,

[quoted text clipped - 10 lines]
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201001/1

Hi Albert,

That is pretty intriguing. Thanks for posting it!

Clifford Bass

Albert D. Kallal wrote:
I have a custom ribbon for reports, which can be used in Runtime A2K,
including a button that will export the information in the report to
Excel -
all works except that the file created is in Excel 95 (I would like 97 -
2003). Is there any way of doing this?

If your code is using:

DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, "c:\1.xls"
You could Try

"Excel 97 - Excel 2003 Workbook (*.xls)"
so:

DoCmd.OutputTo acOutputReport, strReport, "Excel 97 - Excel 2003 Workbook
(*.xls)", "c:\1.xls"
The above works in a2010 beta, but see what happnes in 2007. My VPC version
of access 2007 seems to not work for either of the above, so perhaps it will
work for you...


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201001/1