transferspreadsheet worked in Access 2003, doesn't work in Access2007

[seanhirshberg]

I have a database I built in Access 2003. It has been working just
fine for 1 1/2 years. I just converted it to Access 2007. One of my
instances of transferspreadsheet no longer works. All the other
instances work just fine. I get an "External table not in the
expected format." error message. I've tried using an .xls and .xlsx
extension for my export file. I exported the query using the export
wizard and saved the export steps just in case Access was looking for
some kind of specification file. I renamed the export file, changed
the export type in the transferspreadsheet command line, and doused my
computer with holy water. At one point, I did get the error message
"Recordset must be updateable," but now I'm back to the first error.

 

[Ken Snell \(MVP\)]

i recently learned that tables
or queries with more than ~16000 rows will not export (since that was the
max
number of rows in excel5 worksheet, no matter what version you are
actually
running.

This is not true for TransferSpreadsheet. For ACCESS versions of 97, 2000,
2002, and 2003, TransferSpreadsheet will import up to 65,536 records - the
maximum number of rows for those versions of EXCEL.

The File | Get External Data | Import method will only import up to 16,384
records, which is the maximum number of rows for EXCEL 95.

 

[Ken Snell \(MVP\)]

The default EXCEL version ("Excel Workbook" in the dropdown list for
TransferSpreadsheet in a macro) is EXCEL 2007. Be sure that you specify
"Microsoft Excel 97 - Excel 2003 Workbook" as the EXCEL version in the
macro.

If you're using VBA DoCmd.TransferSpreadsheet, use "acSpreadsheetTypeExcel9"
as the second argument for the method:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TableName", "PathFileName"

Note that the default setting for the second argument of
DoCmd.TransferSpreadsheet has changed from ACCESS 2003 to ACCESS 2007, so if
your original code in 2003 did not specify the type of spreadsheet, the code
will now fail in 2007 for importing EXCEL 2003 workbooks.

 

[Pete D.]

Look at
http://msdn.microsoft.com/en-us/library/bb225982.aspx or in Access help
AcSpreadSheetType Enumeration
to see how to tell transferspreadsheet what version your using to change how
many records it will deal with.