none
error 3011 when i import excel file to my access database

    Question

  • when i import file excel to my access application it give me this error run-time 3011 when i click on debag it show me the error in this first line

        DoCmd.TransferSpreadsheet acImport, 8, "CourseSched", myfile, True, "Course Schedule!" & "A1:EZ65000"
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "AppNewCourses"
        DoCmd.OpenQuery "AppCourseSched"
        DoCmd.SetWarnings True

    how can i resolve it

     

     


    Thanks for any help :) my blog is: http://www.waelk.com
    Monday, June 07, 2010 2:10 PM

Answers

  • That error occurs when an object can't be found. The error should tell you what object it can't find. I assume it is the range, Course Schedule!A1:EZ65000. Check the spelling of the worksheet name.
    Bill Mosca www.ThatllDoIT.com
    • Marked as answer by wael_e Tuesday, June 08, 2010 8:07 AM
    Monday, June 07, 2010 2:41 PM
  • If that isn't the problem then check that it's not caused by the same bug that breaks exports using TransferText. The error message is also wrong, and gives no indication of what the actual problem is. This fault is caused by file called schema.ini which interferes with the export, so check if that file exists in the excel folder.

    See TransferText runtime error 3011

    • Marked as answer by wael_e Tuesday, June 08, 2010 8:07 AM
    • Edited by Dave Ballmer Monday, November 01, 2010 11:39 PM broken link
    Monday, June 07, 2010 3:40 PM

All replies

  • That error occurs when an object can't be found. The error should tell you what object it can't find. I assume it is the range, Course Schedule!A1:EZ65000. Check the spelling of the worksheet name.
    Bill Mosca www.ThatllDoIT.com
    • Marked as answer by wael_e Tuesday, June 08, 2010 8:07 AM
    Monday, June 07, 2010 2:41 PM
  • If that isn't the problem then check that it's not caused by the same bug that breaks exports using TransferText. The error message is also wrong, and gives no indication of what the actual problem is. This fault is caused by file called schema.ini which interferes with the export, so check if that file exists in the excel folder.

    See TransferText runtime error 3011

    • Marked as answer by wael_e Tuesday, June 08, 2010 8:07 AM
    • Edited by Dave Ballmer Monday, November 01, 2010 11:39 PM broken link
    Monday, June 07, 2010 3:40 PM
  • I am having a similar problem!  Here's what I have discovered so far ... in some of my Excel files the name is stored as just the name ... and sometimes it is sotred as sheet and name .... haven't found the pattern why, though.  Looks like this in the Define Name window:

    File A:                                                 File B:

    MyRange                                            MyRange                              MySheet
    NextRange                                          NextRange

    So, when I do the TransferSpreadsheet I get the 3011 error if I haven't styled the name just right .... problem is I don't know when I'll need

      DoCmd.TransferSpreadsheet acImport, 8, "MyDataTable", myfile, True, "MyRange"

    and when I will need

      DoCmd.TransferSpreadsheet acImport, 8, "MyDataTable", myfile, True, "MySheet!MyRange"


    I'd LOVE some insight on why the range name styling seems inconsistent.  I'm sure there is a reasonable explanation, but I haven't figured it put yet.

     

    Monday, November 01, 2010 7:27 PM
  • I'd LOVE some insight on why the range name styling seems inconsistent.  I'm sure there is a reasonable explanation, but I haven't figured it put yet.

    There seems to be a number of inconsitencies and bugs that have remained since Office 2000 was released. The original 3011 bug was discovered in Access 2000 VBA when exporting to Word Merge format. This bug remains in all subsequent versions of Access VBA, and also affects exporting to Excel format. The name styling changes you had to do are probably just treating the symptoms of this bug, and are not a cure for the actual disease, and are definitely not the cause.

    See ACC2000: Exporting to Word Merge Format After Renaming Field Causes Error

    Use one of the following methods to correct this problem.

    Method 1

    Open the Schema.ini file with a text editor such as Notepad, and then delete the section that you are having trouble with. The section starts with the name of the text file in brackets. For example, if you export the Shippers table, the section would start on the line that has [Shippers.txt]. The section ends on the last line above the beginning of the next section.

    Method 2

    Open the Schema.ini file with a text editor such as Notepad, and then correct the field names that you are having trouble with. For example, if you export the Shippers table and later change the CompanyName field to CName, you would change the line that read

    Col2=CompanyName Char Width 40

    to read as follows:

    Col2=CName Char Width 40

    Method 3

    Delete the Schema.ini file, and then export the table or query.

    Monday, November 01, 2010 11:51 PM
  • I didn't know that you could alternate between "MyRange" and "MySheet!MyRange". I know this is kind of late but this info helped me alot; all I do now is use "MySheet!MyRange", and then On Error GoTo "MyRange"

    Thursday, October 16, 2014 8:39 PM