none
TransferSpreadsheet using VBA in Access 2007

    Question

  • I keep getting this:

    Run-time error 3434:

    Cannot expand named range.

    Here is my (simple) code:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SAP Query", _

    \\NYC\GB\ACCOUNTING\05_May\Reports\ReportingFile.xlsb

     

     

    I got this error, both using VBA and using the right-click, export to Excel method of TransferSpreadsheet.

     

     

    I would be happy with either:

    ReportingFile.xlsb

    ReportingFile.csv

     

    Maybe this is the problem:  acSpreadsheetTypeExcel9

     

    I think this is the correct version for 2007 thought.  Can someone please confirm??

     

    Thanks!!

     

     

    Tuesday, June 07, 2011 7:55 PM

Answers

  • Did you test my above suggestion and did it work?

    Either way with Macro or with VBA it should work.

     

    HTH,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by ryguy72 Thursday, June 09, 2011 8:46 PM
    Wednesday, June 08, 2011 2:09 PM

All replies

  • Checking in Acc03 I have the acSpreadsheetTypeExcel9 constant - it appears that Excel9 pertains to the pre-2007 formats.  I'm no excel wizard by any means, and don't have Access 2007 handy, but Office 2003 is version 10 and Office 2007 is 11 (I think).  Does Access VBA (2007) give a Excel11 option (and what's the .xlsb format?)

    hth


    Access Wiki: http://www.utteraccess.com/wiki
    Wednesday, June 08, 2011 12:53 AM
  • The .xlsb format is a Binary File Format in Excel.

    acSpreadsheetTypeExcel 9 is the Excel 2000 Format.

     

    Try this instead:

    DoCmd.TransferSpreadsheet acExport, , "SAP Query", "\\NYC\GB\ACCOUNTING\05_May\Reports\ReportingFile.xlsb"

     

    That should do the trick.

     

    HTH,


     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, June 08, 2011 4:52 AM
  • Thanks everyone.  As it turns out, TransferSpreadsheets was disabled by default.  I enable it by following these instructions:

    http://accessjunkie.com/faq_02.aspx

     

    Then, it was simply a matter of creating a Macro, and converting it to a VBA procedure.

     

    I won’t post the final code, because it will be different for every user, but follow the steps I mentioned above, and it will work fine.

    Wednesday, June 08, 2011 1:47 PM
  • Did you test my above suggestion and did it work?

    Either way with Macro or with VBA it should work.

     

    HTH,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Marked as answer by ryguy72 Thursday, June 09, 2011 8:46 PM
    Wednesday, June 08, 2011 2:09 PM