Export settings to Excel 2003

[Guest]

Good afternoon,

I setup a macro to transfer (Export) a spreadsheet to Excel. In my macro
settings I set:

Transfer Type: Export

Spreadsheet Type: Excel 8-10. (I select between Excel 3, Excel 4, Excel 5-7,
or Excel 8-10. I assume Excel 8-10 is the most recent version of Excel. I
usually use 8-10)

Table Name: Eastvale Sales Prod Report
File Name:C:\path\filename.xls
Fieldnames: Yes

I then save and run this macro. Export works fine. BUT if I change any data
and save that Workbook, a message box pops up saying
"<filename> is a Microsoft Excel 5.0/95 Workbook, do you want to overwrite
this w/ the latest Excel Format?"

This occurs if I use Excel 5-7 or Excel 8-10 settings.
I also tried using this VBA statement to export
DoCmd.OutputTo acOutputTable, "ESCROW OFFICES",
"MicrosoftExcelBiff8(*.xls)", "C:\DATABASES\Escrow Graph Stats.xls", True,
"", 0

But the same message occurs if saving changes. What do I need in a macro or
VBA expression to export that file in a 2003 version and stop this message
box from appearing? Thanks.

Cordially,

 

[Ken Snell [MVP]]

Use TransferSpreadsheet, not OutputTo.

 

[Guest]

Ken, thanks for your pompt assistance.

Yes I actaully was using a Transfer Spreadsheet command. In my macro I set
command to "Transfer spreadsheet" and that opens up a series of settings at
the bottom and that's where I plug in my specifics:
Transfer Type: Export
Spreadsheet Type: Excel 8-10
Table Name: Eastvale Sales Prod Report
File Name:C:\path\filename.xls
Fieldnames: Yes

If I then save this macro and save as a module, the code comes out:
DoCmd.TransferSpreadsheet acExport, 8, "SALES OFFICE PROD REPORTS",
"C:\Prudential Operations Graphs\Sales Office Prod Reports\2005\SALES OFFICE
PROD REPORTS.XLS", True, ""

But if I run this and change any data in Excel and then try to save the
file, I first get a message asking if I want to save changes, if I click yes
then I get message that this file is in 5.0/95 version again. What do I do to
fix this? Thanks Ken.

 

[Ken Snell [MVP]]

Hmmm.... what you have for your macro and the VBA code is correct, and
should not be causing the version format issue that you're seeing.

Which version of ACCESS and which version of EXCEL are you using?

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

I am using Access 2003 SP1 and Excel 2003 SP1.

Hmmm.... what you have for your macro and the VBA code is correct, and
should not be causing the version format issue that you're seeing.

Which version of ACCESS and which version of EXCEL are you using?

 

[Ken Snell [MVP]]

Let me check with some other people... I have not run across this issue
before using your code as posted.

May take me short time before I get back to you.....

 

[Guest]

Sound good. Thanks Ken

Let me check with some other people... I have not run across this issue
before using your code as posted.

May take me short time before I get back to you.....

 

[Ken Snell [MVP]]

Brent -

So far, I've not found any useful information.

Have you tried repairing the Office installation?
--

Ken Snell
<MS ACCESS MVP>