Results 1 to 4 of 4
  1. #1
    mkc80 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    56

    Import a specific excel worksheet in to a table in MS Access 2007

    Hi,

    I have been trying to import an excel sheet (a specific excel sheet in the workbook) using this method but I get an error:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customer", "C:\Download.xlsx", True, "CustomerFormatted"

    where "CustomerFormatted" is the sheet I am trying to import in to the "Customer" table.



    The error I get is Run Time Error 3011, The MS Access engine could not find the object "CustomerFormatted" make sure its name is spelled correctly ....

    Is there any other way I can import an excel sheet in to an access table?

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Just add an exclamation mark at the end of your sheet name argument, i.e.
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Customer", "C:\Download.xlsx", True, "CustomerFormatted!"


  3. #3
    tanvi is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    100
    Please check this article, it has good explanation about the error: http://www.sheroz.com/articles/ms-access-and-dbf-files

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Please check this article, it has good explanation about the error: http://www.sheroz.com/articles/ms-access-and-dbf-files
    One thing to be aware of is that there are often many different situations that return the same error number. In looking at the problem the poster was having, it looks like he was trying to import Excel files, not DBF files, so I could be wrong, but I don't think that the article referenced applies in this case.

    The funny thing was someone in our office had the exact same problem as this poster did a few weeks ago, where they were trying to import an entire sheet from an Excel file, just referencing the Sheet Name as the range. And they had the exact same cause, they were missing the exclamation point at the end of the sheet name. I tried it out myself, and it does return that error message if you leave it off, but works if you include it. So I am guessing that is the most likely cause of the error the member is receiving.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-05-2011, 09:01 AM
  2. Disabling Excel import to a specific table
    By JoshD in forum Import/Export Data
    Replies: 0
    Last Post: 04-15-2011, 02:26 PM
  3. Import excel worksheet by range with non identical headers
    By snoopy2003 in forum Import/Export Data
    Replies: 1
    Last Post: 03-14-2011, 01:04 AM
  4. VBA to Import Excel Worksheet
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 10:53 AM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums