Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1947
  • Last Modified:

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
0
ferranti
Asked:
ferranti
  • 4
  • 3
  • 2
2 Solutions
 
peter57rCommented:
I don't think you can use the xlsx extension.  Change it to xls and you should be able to export OK.
0
 
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:
Hi Scott
xlsx doesn't work with acSpreadsheetTypeExcel12 (!)
It does work with acSpreadsheetTypeExcel12XML
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now