Transfer spreadsheet from Access 2007 to Excel 2007

I have recently installed Office 2007 on my computer. I want to transfer a spreadhseet from Access 2007 to Excel 2007. Previously, using Access 2003 and Excel 2003 I used the following code.

FileNme = "C:\Documents and Settings\Winter.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TbleNme, FileNme, True

I can open up this file in 'compatability mode'. However, I want to export to Excel 2007. I tried using the following code, but I can't open up the Excel workbook.

FileNme = "C:\Documents and Settings\Winter.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TbleNme, FileNme, True

Please help!
Thanks
ferrantiAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might try:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TbleNme, FileNme, True

or

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12XML, TbleNme, FileNme, True
0
 
peter57rCommented:
I don't think you can use the xlsx extension.  Change it to xls and you should be able to export OK.
0
 
peter57rCommented:
Hi Scott
xlsx doesn't work with acSpreadsheetTypeExcel12 (!)
It does work with acSpreadsheetTypeExcel12XML
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Didn't know that ... but I'm just getting my feet wet with 2007 (and not loving it either <g>).
0
 
ferrantiAuthor Commented:
Hi,

I've tested this out

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12XML, TbleNme, FileNme, True

And it seems to work OK.

Thanks for your help.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you use my EXACT suggestion, but you grade this a B?? Any reason for that?
0
 
ferrantiAuthor Commented:
Erm.... I'm unsure how to react to this.

I'm a teacher and I only give A's to absolutely perfect pieces of work upon which no improvement could be made. From your answer I wasn't sure whether to use .xls or .xlsx at first, and also your first suggestion didn't work. If you had just said use .xlsx and  acSpreadsheetTypeExcel12XML and this will work I would probably have given you an A.

I'm sorry if you think your answer should be an A, but I am very grateful for the time & help you provided. I hope that answers your questions.

Thanks
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Errm ... I'm QUITE sure how to react to this, but I'll let the mods decide on this.

0
 
ferrantiAuthor Commented:
Thank you for your clarity on this issue. I had no idea how important the grading system was on Expert's Exchange.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.