We help IT Professionals succeed at work.

Transfer spreadsheet from Access 2007 to Excel 2007

ferranti
ferranti asked
on
Medium Priority
1,987 Views
Last Modified: 2013-11-27
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
I don't think you can use the xlsx extension.  Change it to xls and you should be able to export OK.
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
You might try:

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

or

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Commented:
Hi Scott
xlsx doesn't work with acSpreadsheetTypeExcel12 (!)
It does work with acSpreadsheetTypeExcel12XML
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Didn't know that ... but I'm just getting my feet wet with 2007 (and not loving it either <g>).

Author

Commented:
Hi,

I've tested this out

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

And it seems to work OK.

Thanks for your help.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
So you use my EXACT suggestion, but you grade this a B?? Any reason for that?

Author

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
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Errm ... I'm QUITE sure how to react to this, but I'll let the mods decide on this.

Author

Commented:
Thank you for your clarity on this issue. I had no idea how important the grading system was on Expert's Exchange.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.