MS Access runtime 3011 error - variable ranges

Question

Any assistance with this will be very much appreciated.

I'm trying to fill a table in Access with a spreadsheet, and specifying file, sheet and range to read. Unfortunately, the range in excel is not prefixed.

Run-time error 3011: The Microsoft Access database engine could not find the obeject 'SheetName$A:Z10'. Make sure the object exists and that you spell its name and the path name correctly. If 'SheetName$A:Z10' is not a local object, check your network connection or contact the server administrator.

DoCmd.TransferSpreadsheet acImport, 10, "TableName", URL & "\" & fileimport, True, "SheetName!" & "A1:Z" & N - 1

Where N is the number of rows. As you can see, the strings that specify the tab and range debug fine as SheetName!A1:Z10 (for instance N=11), but in the error message, Access has replaced "!" with "$", and the whole thing bugs out.

Any ideas why this is happening, and how to fix it?

Full procedure follows.

Private Sub rapportoExcel_succ(singoloExcel As Integer)

Dim nomefile, SzSQL, URL, Tabella, foglio1, foglio2 As String
Dim N, M As Integer
Dim Workbook1 As Object
Dim ImportList As Boolean
Set wks1 = DBEngine.Workspaces(0)
Set db = wks1.Databases(0)
Set Workbook1 = CreateObject("Excel.Application")

If IsNull(URL_name) Then
    URL = CurrentProject.Path
    Else
    URL = URL_name
End If

If IsNull(Date1) Then
    Else
    dstep = Date1
End If

qa = MsgBox("Are you sure?", vbYesNo)
If qa <> 6 Then Exit Sub

If Me!ImportList = -1 Or Me.Recordset.EOF Then Me.Recordset.MoveFirst

Do
     
Me.Repaint

If Me!ImportList = -1 Then
     fileimport = Me!nomefile & ".xlsx"
     Else
     fileimport = NomeDoc1 & ".xlsx"
End If
     
Workbook1.Workbooks.Open URL & "\" & fileimport, False
Workbook1.Visible = True
Workbook1.DisplayAlerts = False

With Workbook1.ActiveWorkbook
    .Unprotect ("a")
    foglio1 = .Sheets(3).Name
    .Sheets(3).Select
    .Unprotect ("a")
    .Sheets.Add
    .Sheets(3).Move before:=Sheets(1)
    .Sheets(1).Name = "SheetName"
    
    .Sheets("FullTable").Select
    .Sheets("FullTable").Range("C2").Select
    
End With

Workbook1.ActiveWorkbook.Sheets("FullTable").Range("C2").FormulaR1C1 = "=COUNTA(R[1]C:R[10000]C)"

N = Workbook1.ActiveWorkbook.Sheets("FullTable").Range("C2").Value + 2

If IsNull(dstep) Then Else dstep = Workbook1.ActiveWorkbook.Sheets("FullTable").Range("B1").Value

Workbook1.ActiveWorkbook.Sheets("FullTable").Range("X3:X" & N).Value = dstep 

Workbook1.ActiveWorkbook.Sheets("FullTable").Range("A3:X" & N).Copy

Workbook1.ActiveWorkbook.Sheets("SheetName").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Workbook1.CutCopyMode = False

Workbook1.ActiveWorkbook.Sheets("SheetName").Columns("I").Delete Shift:=xlToLeft

Workbook1.ActiveWorkbook.Sheets("SheetName").Columns("J").Delete Shift:=xlToLeft

Workbook1.ActiveWorkbook.Sheets("SheetName").Columns("K").Delete Shift:=xlToLeft

Workbook1.ActiveWorkbook.Sheets("SheetName").Range("A1").FormulaR1C1 = "F1"

Workbook1.ActiveWorkbook.Sheets("SheetName").Range("B1").FormulaR1C1 = "F2"

With Workbook1.ActiveWorkbook
    .Sheets("SheetName").Select
    .Sheets("SheetName").Range("A1:B1").AutoFill Destination:=.Sheets("SheetName").Range("A1:Z1"), Type:=xlFillDefault
    
End With

DoCmd.TransferSpreadsheet acImport, 10, "TableName", URL & "\" & fileimport, True, "SheetName!" & "A1:Z" & N - 1

ActiveWorkbook.Close SaveChanges:=False
If Me!ImportList = -1 Then Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF Or Me!ImportList = 0

MsgBox "Import complete" & IIf(Me.ImportList = 0, "1 group", Me.Recordset.RecordCount & " groups")

Workbook1.Quit

End Sub

Answer 1

The problem with the code is that variables and object are not well defined, starting with Excel App called WorkBook1 instead of ExcelApp. This brings confusion. Rewriting the code and avoiding "Active__" objects fixed all.