Solved

3011 error importing spreadhseet information

Posted on 2012-12-22
10
357 Views
Last Modified: 2012-12-27
One of the pieces of a system I am developing for a client involves importing data entered on a spreadsheet.  I’ve done it many times in MS access apps without issue.

Originally the spreadsheet to be imported had two tabs, the names of which would change from spreadsheet to spreadsheet.  Both tabs contained data in the same format.  Same number of columns with each column representing the same information on each tab.

I developed logic to import data from these two tabs.  The first piece of logic determines the name of the tab and then I imported the data on the spreadsheet into a table with the same name as the tabs using the logic below.  The table is created by the Transferspreadsheet command.

Prior to the logic below there is a file selection box that appears to allow the user to select the spreadsheet they want processed.  That information, including the full path and name of the spreadsheet is stored in the field ‘newSpreadName’.  The file slection logic is the same in both the two tab and three tab versions of the below logic.

This is the logic that I used to determine the names of each of the 2 tabs and then import the information on each tab into a table with the same name.

'
Dim wkTabName As String
Dim tabNamePerm As String
Dim tabNameAddend As String
Dim i As Integer
'
Dim objExApp As Excel.Application
'
Dim objWkbook As Excel.workbook
Dim objSheet As Worksheet
Set objWkbook = GetObject(newSpreadName)
For i = 1 To 2
    Set objSheet = objWkbook.sheets(i)
    wkTabName = objSheet.Name
    '
    ' In case this is a repeat import, delete the table
    '
    deleteTableObject (wkTabName)
    '
    If i = 1 Then
        tabNamePerm = wkTabName
    Else
        tabNameAddend = wkTabName
    End If

    On Error GoTo ImportErrHandler
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, wkTabName, newSpreadName, , wkTabName & "!A3:T1000"

Next

The end result of this logic is that two tables with the same name as the table were dynamically created and populated with the data form the respective tab, by the ‘Transfer spreadsheet command.

It worked perfectly.

The user has since change the import spreadsheet to contain three tabs with differing formats.  The first 11 columns on each tab are the same, after that the data and meaning of the columns change based on the tab.

I thought this would be an easy adaptation and revised the logic as follows:

Dim wkTabName As String
'
Dim tab1Name As String
Dim tab2Name As String
Dim tab3Name As String
'
Dim i As Integer
'
Dim objExApp As Excel.Application
'
Dim objWkbook As Excel.workbook
Dim objSheet As Worksheet
Set objWkbook = GetObject(newSpreadName)
For i = 1 To 3
    Set objSheet = objWkbook.sheets(i)
    wkTabName = objSheet.Name
    '
    ' In case this is a repeat import, delete the table
    '
    deleteTableObject (wkTabName)
    '
    Select Case i
        Case 1
            tab1Name = wkTabName
            Debug.Print "wkTabName             "; wkTabName
            Debug.Print "newSpreadName         "; newSpreadName
            Debug.Print "wkTabName & !A3:T1000 "; wkTabName & "!A3:T1000"
           
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, wkTabName, newSpreadName, False, wkTabName & "!A3:Q1000"

        Case 2
            tab2Name = wkTabName
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, wkTabName, newSpreadName, False, wkTabName & "!A3:T1000"
        Case 3
            tab3Name = wkTabName
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, wkTabName, newSpreadName, False, wkTabName & "!A3:P1000"
        End Select
Next


On the very first ‘TransferSpreadsheet’ I get:Error 3011, The Microsoft jet engine could not find the object ‘Monthly Bus Pass – January 2013$A3:Q1000’

As you can see from the attached 3 tab spreadsheet, it does exist and I can’t determine what the issue is.  It is using the name it pulled from the tab in the above logic, so it has to exist.

This is the contents of the fields shown in the debug.print statements, which is exactly as I would expect:

wkTabName                          MONTHLY BUS PASS - January 2013
newSpreadName                  C:\My Documents\Access_Databases\DPWApp\ThisOne_Liberty - January 2013 Transportation.xls
wkTabName & !A3:T1000      MONTHLY BUS PASS - January 2013!A3:T1000

I don’t know what fell apart when I revised the logic to process three tabs with varying formats instead of two with the same format.  Conceptually the process should be the same.  However, not only am I having issues but the issue isn’t with the second or third tab, but the first.

I attached the two tab spreadsheet and the three tab spreadsheet.  We are just in testing so the data is nonsense.  This particular two tab example doesn't have data on the second tab but it works even if it does.

Any help would be greatly appreciated.
TwoTab-January2013.xls
ThreeTab-January2013.xls
0
Comment
Question by:mlcktmguy
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:als315
ID: 38716466
I have no problems with your code and uploaded files (but in Access 2010).
Dxls3.mdb
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 38716493
Thank you for checking it out.  When I try to run the MDB that you sent I have to fix the references to Microsoft Office 14 and MS Excel 14 since my version is older.

Once I fix them and run your version I get exactly the same error.  In one way it's frustrating but in another maybe this information will lead to a solution.

In all of this my biq question is, why did it work perfectly fine on the 2 tab and then have issues on the 3 tab.

I appreciate your help.
0
 
LVL 39

Accepted Solution

by:
als315 earned 250 total points
ID: 38716531
Seems problem is in Access version. Try this sample (excel late binding - no need to change reference and excel is closed before import)
Dxls3.mdb
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 38716972
Once again thanks you but I still get the exact same error in the same place.

When I look at the references I see a Missing reference "MS Office 14 Access Db Engine"

I don't see any corresponding entries in my reference list to relace it with.

Also, I don't undersatnd what you mean by 'late binding' perhaps I'm doidn something wrong when I execute your version.  I am stepping into 'step into' it from the debugger.
0
 
LVL 39

Expert Comment

by:als315
ID: 38717028
I have no Access 2003 now. Tomorrow I'll be able to test it in Access 2003.

With late binding you can not declare Excel library and so you should not change it in different Access versions:
Dim objWkbook As Object
Set objExApp = CreateObject("Excel.Application")
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Author Comment

by:mlcktmguy
ID: 38717414
als315, once again, thanks for helping out.  In the meantime, for some reason I developed a hunch that it wasn't the logic or Access that was having issues but that the issue was with the spreadsheet.

To check this out I modified the old spreadsheet that processed perfectly but was in the two tab format with both tabs having the same exact layout, into a three tab spreadhseet in which the tabs had the varying layouts on the supplied 3 tab spreadhsheet (ThreeTab_January2013.xls).

When I did that and ran the new spreadsheet (Two_Now_3VaryingTabs_January2013.xls), which I attached,  into the new 3 tab logic there were no issues whatsover.  The three work tables were generated in the same format as the respective tabs.

On one hand this is good news but on the other hand it is obviously not.  The original 3 tab spreadsheet causing the issues was supplied by the client.  Whatever issues are with it will have to be identified, otherwise in production, it won't work.  The client I am developing this app for only has Access 2003 so upgrading is not and option.

I know the organization supplying the spreadsheet is upgraded to Office 2007 or 2010 because originally they supplied a spreadsheet in xlsx format.  I let them know that wouldn't work so they supplied this one in xls format.

I am wondering if when they converted or created the 3 tab spreadsheet in xls it retained some of the 2007 formatting.  That would explain why you could process it in Access 2010.  It will be intersting to see if you run into issues tomorrow when you try to process in Access 2003.

Of course they also supllied the two tab xls that worked fine.  All very confusing.

Once again, thanks for your help and interest in my issue.
Two-Now-3VaryingTabs-January2013.xls
0
 
LVL 39

Expert Comment

by:als315
ID: 38718001
Yes, problem is in Access 2003. But it can't download 2 sheet file also. It does not understand range in sheet. As I can see, you need range only for removing first two lines. You can try link files and add data to table with query or you can delete first two lines in excel sheets and import whole sheet without range.
Sample for second way is included. I'm trying to find value "Date of Approval" in cell A2 and, if it is found, delete 2 first rows.
Dxls3.mdb
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 38718471
Thank you for checking that out but your results differ from mine.  Did you read my last post, prior to this one?  If not, please do.

I was always able to process the 2 tab spreadsheet, pulling in ranges with the original logic.  It was only when I revised the logic to handle three tabs and tried the 3 tab spreadsheet they had sent that I had issues.

Coulld you try to import the 3 tab version I atached to my last post(Two-now-3varyingtabs-January2013.xls)?

It imports perfectly using my revised logic.  All three tabs are pulled, all three temporay tables with the name of the respective tab are built.

My suspicion is that there is something different about the 3 tab spreadsheet that the client supplied which made it only importable in Access 2007 and above.
0
 
LVL 39

Expert Comment

by:als315
ID: 38718568
Yes, I've read it and now I've tested again 2 sheet file and it was imported. May be I've tested wrong file.
I've tested your last file and it is working. I can't explain why.
I see in error missing "!" between sheet name and range:
Error 3011, The Microsoft jet engine could not find the object ‘Monthly Bus Pass – January 2013$A3:Q1000’
As you can see, Access adds $ sign after sheet name, but on any reason removes !
I don't think you can control this behaviour and can only propose to remove range in transferspreadsheet. In this case file always is imported without problems.
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 38726032
The problem is that the spreadsheet being sent was not compatable with Access 2003 alhtough it was easily processed in Access 2007.  I notifed the organization supplying the spreadsheet and they are making revisions to ensure cmpatablility with Access 2003.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now