Solved

3011 error importing spreadhseet information

Posted on 2012-12-22
10
360 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

16 Experts available now in Live!

Get 1:1 Help Now