How to reproduce
- Create a new database.
- Create the test data by executing the following code:
Sub testdata() Dim i As Long CurrentDb.Execute "CREATE TABLE Table1 (id AUTOINCREMENT, field1 TEXT(255))" For i = 1 To 31 CurrentDb.Execute "INSERT INTO Table1 (field1) VALUES ('a')" Next CurrentDb.Execute "INSERT INTO Table1 (field1) VALUES (' ')" CurrentDb.Execute "INSERT INTO Table1 (field1) VALUES ('a')" End Sub
- Execute the following command (but replace the file location with a path to YOUR desktop):
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Users\Username\Desktop\excel.xls"
- Look into the generated Excel file and note that it looks fine. Close it
- Execute the same TransferSpreadsheet command again!
Expected result: The file has not changed.
Actual result: The first 32 rows of the spreadsheet (including the headers) are gone.
Discussion: Note that the issue only occurs if (a) the 32nd record contains a space in Field1 and (b) TransferSpreadsheet is executed twice. Tested with Access 2007 and 2010.
Any thoughts on this? Maybe any chance to get this fixed?
This is a quick note to let you know that we are doing research on this issue. We will let you know if there is any progress.
Have a nice day.
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
I can confirm that this is a problem in A2010 as well as A2013.
The problem goes away (of course) if you modify the code to:
Const filename As String = "C:\Users\Tom\Desktop\excel.xls"
If Dir$(filename) <> "" Then Kill filename
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Users\Tom\Desktop\excel.xls"
-Tom. Microsoft Access MVP
I don't know of any bugs in the TransferSpreadhseet method. I've used this process thousands of times in my career; maybe tens of thousands of times. Your code definitely looks weird to me. Follow Tom's example, and/or see the links below for the traditional way of using the TransferSpreadsheet method.
This is one of my favorite resources:
Yoyo, Tom, thanks for looking into it and confirming the issue.
Tom: I agree. That's the workaround¹ we are currently using to avoid the issue.
ryguy72: Thanks, but this is not production code, and we are aware of the TransferSpreadsheet documentation. My code is a so-called minimum working example, designed to demonstrate the bug in question (and nothing else). It's purpose is to aid the developers in fixing it by providing them with a test case that reliably reproduces the issue.
¹ TransferSpreadsheet has a nice property: The table/query is exported into a sheet of the Excel file with the same name as the table/query, without modifying the remainder of the file. For example, if excel.xls existed, but did not have a sheet called "Table1" yet, it would add the sheet and leave the rest of the file intact. This allows for aggregating multiple data sets into a single file. Obviously, this feature is lost when removing the file first. However, it's something we can live with.
- Edited by Heinzi.at Friday, February 01, 2013 6:15 AM typo
I do like Tom. I came across this problem a long time ago, and found this fix and never looked back.
Basically, regardless of what command you are using, if you are creating a new file, it is always best to delete an existing one, if it exists.
- Edited by Daniel PineaultMVP Friday, February 01, 2013 3:08 AM