none
DoCmd.TransferSpreadsheet query transfer not expanding in excel

    Question

  • Hello,

    I have developed a transfer process taking access (2003) queries and transfering them to individual excel files.  These updates are done automatically when the user opens the database.  I'm having a problem on the Excel end where the entire result of the Access query is not being transferred.  The Excel file is being updated and the formatting is unchanged but no additional rows are being added - the spreadsheet isn't expanding???

    This is an example of what I'm using to make these transfers:

    Private Sub Form_Open(Cancel As Integer)

    DoCmd.TransferSpreadsheet acExport, _

    acSpreadsheetTypeExcel9, "qry_NAME", _

    "\\{UNC}\Shared\Data_Access\...\...\...\Projects\#Reports\Output_Reports_from_db\Open_Submissions.xls", , "Open_Submissions"

    [ I’ve truncated the path a bit to save space ]

    Any suggestions as to why the entire query export isn't showing up in excel?

    Thank you,
    Bill


    Bill

    Friday, February 01, 2013 4:59 PM

Answers

  • I just looked up DoCmd.TransferSpreadsheet in the help file, and it explicitly states that you can't use a range name in an export (which is what "Open_Submissions" is!)

    Of course, the entry states "When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.", so I'm not sure why you're not getting an error (do you perhaps have On Error Resume Next specified?)

    In any case, try removing that and see if it works:

    DoCmd.TransferSpreadsheet acExport, _
      acSpreadsheetTypeExcel9, "qry_NAME", _
      "\\{UNC}\Shared\Data_Access\...\...\...\Projects\#Reports\Output_Reports_from_db\Open_Submissions.xls"


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Proposed as answer by Dummy yoyoModerator Tuesday, February 05, 2013 9:28 AM
    • Marked as answer by BillA83 Tuesday, February 05, 2013 8:08 PM
    Saturday, February 02, 2013 1:58 PM

All replies

  • How many rows are correct? 64K?

    This {UNC} piece is new to me. Do you have a reference explaining this should be there?


    -Tom. Microsoft Access MVP

    Saturday, February 02, 2013 5:07 AM
  • Hi Tom,

    I didn't want to provide the shared directory path, which in this case is the numerical Uniform Naming Convention (UNC).

    As for the number of rows - it's actually quite small, so far only about 30 rows are being received in excel.

    It's as if excel is not allowing more rows than what was established in the first transfer.

    Thanks,
    Bill


    Bill

    Saturday, February 02, 2013 1:46 PM
  • I just looked up DoCmd.TransferSpreadsheet in the help file, and it explicitly states that you can't use a range name in an export (which is what "Open_Submissions" is!)

    Of course, the entry states "When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.", so I'm not sure why you're not getting an error (do you perhaps have On Error Resume Next specified?)

    In any case, try removing that and see if it works:

    DoCmd.TransferSpreadsheet acExport, _
      acSpreadsheetTypeExcel9, "qry_NAME", _
      "\\{UNC}\Shared\Data_Access\...\...\...\Projects\#Reports\Output_Reports_from_db\Open_Submissions.xls"


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Proposed as answer by Dummy yoyoModerator Tuesday, February 05, 2013 9:28 AM
    • Marked as answer by BillA83 Tuesday, February 05, 2013 8:08 PM
    Saturday, February 02, 2013 1:58 PM
  • Thanks Tom & Doug,

    Sorry for my delayed response - out of the office unexpectedly.

    I used Doug’s advice and removed the range name but that didn’t fix the problem.  I decided to remove the existing excel file and work from a clean export.  I think that did the trick – it appears the spreadsheet that was to be updated had a set printing area of formatting that precluded changes beyond a set row. 

    Thanks again.
    Bill


    Bill

    Tuesday, February 05, 2013 1:11 PM