Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

3011 error importing spreadhseet information

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
mlcktmguy
Asked:
mlcktmguy
  • 5
  • 5
1 Solution
 
als315Commented:
I have no problems with your code and uploaded files (but in Access 2010).
Dxls3.mdb
0
 
mlcktmguyAuthor Commented:
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
 
als315Commented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
mlcktmguyAuthor Commented:
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
 
als315Commented:
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
 
mlcktmguyAuthor Commented:
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
 
als315Commented:
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
 
mlcktmguyAuthor Commented:
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
 
als315Commented:
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
 
mlcktmguyAuthor Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now