none
Bug in TransferSpreadsheet (when executing it twice with certain data)

    Question

  • How to reproduce

    1. Create a new database.
    2. 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
    1. 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"
    1. Look into the generated Excel file and note that it looks fine. Close it
    2. 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?

    Wednesday, January 30, 2013 5:08 PM

Answers

  • I have reproduced the issue in-house, and submitted an Office bug for this case. Our developer engineer will check on it, and if there is any update, I will let you know.

    • Marked as answer by Heinzi.at Tuesday, February 05, 2013 1:28 PM
    Tuesday, February 05, 2013 5:33 AM

All replies

  • 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.


    Yoyo Jiang[MSFT]
    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.

    Thursday, January 31, 2013 7:09 AM
    Moderator
  • 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

    Thursday, January 31, 2013 2:24 PM
  • 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.

    http://msdn.microsoft.com/en-us/library/office/ff844793.aspx

    http://www.bluemoosetech.com/microsoft-access-tutorial.php?jid=3&title=Microsoft

    This is one of my favorite resources:

    http://www.accessmvp.com/KDSnell/EXCEL_Export.htm


    Ryan Shuell

    Thursday, January 31, 2013 5:07 PM
  • 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.

    Greetings
    Heinzi

    ¹ 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
    Thursday, January 31, 2013 8:50 PM
  • 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.


    Daniel Pineault, 2010 Microsoft MVP
    http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, January 31, 2013 9:00 PM
  • I have reproduced the issue in-house, and submitted an Office bug for this case. Our developer engineer will check on it, and if there is any update, I will let you know.

    • Marked as answer by Heinzi.at Tuesday, February 05, 2013 1:28 PM
    Tuesday, February 05, 2013 5:33 AM
  • Scott,

    Great, thanks a lot!

    Tuesday, February 05, 2013 1:28 PM