Export Access data with TransferSpreadsheet to Excel
I've read all the topics that appear relevant ... nayda ! So here's my issue :
Dim FileName as string
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "tblNachaTransactions", FileName
The TransferSpreadsheet function exports to an Excel file just fine ... BUT ... I cannot delete the resultant '*.xls' filename once I've finished and closed the MS Access application; the Excel file is not open and neither is Excel so it's logical to want to delete a test file.
I've tried moving to Excel, opening the file, and saving it or 'closing' it ... no help.
I've check for 'shared' users from within Excel, none are indicated.
I, admittedly, left off the last two 'column names' and 'range' parameters ... column names at this point were irrelevant (just use default), so my thought was let the function derive a range as the data write progresses ... all the data is written.
The documentaion is unclear, so I really need some expert help ... btw ... if you creat an Excel object, write the data, and use the close method of the Excel object, this is not a problem .. delete the file anytime you want.
[1363 byte] By [
Hawksway] at [2008-1-10]
I have a similar problem working with Office 2003. I have an Access database that exports data from several crosstab queries, using macros and the TransferSpreadsheet option, into sheets within multiple Excel Workbooks. The source table has only three fields, however the number of columns produced by these queries grows each week; until at the end of the financial year, there are 54+/- columns.
At the end of the last financial year I saved the workbooks as 2006_07, and created new 2007_08 books to be used in the new financial year. I deleted the existing data from the spreadsheets ready for the new years data. Unfortunately, now, whenever the export process is used, an error message occurs stating that there are "Too many fields defined" runtime error 3190.
For earlier versions up to Access 97, this is covered at: http://support.microsoft.com/kb/128221/zh-cn where it states that replacing the original source table by using the "save as", (structure & data) will overcome the problem.
However, there is no mention of why it occurs in Access 2003 in this situation, or how to work around it... The number of columns produced by the crosstab queries, has now reduced from 52 back to the first weeks of this year.
So, I am wondering, if the internal column count that Microsoft Access uses to track the number of fields in a table also occurs in crosstab queries to establish whether the count has reached 255; thereby causing this error?
Can someone please help?
Cheers.
XRC
