troubleshooting Question

3011 error importing spreadhseet information

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
10 Comments1 Solution424 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros