Important Dynamic Range from Excel

[Mennochio]

Hi guys,

I am trying to import a dynamic range I made in Excel into a table in access. I use the code below to import a range from excel into a table. The range I have in excel is defined as

=OFFSET(ybport!$A$2,0,0,COUNTA(data!$A:$A)-1,5)

and is named yieldbookMort. The VBA code below in access errors out when I try to import that named range. It thinks it doesn't exist? Any ideas besides using a static name??

Public Sub ImportExcel()
'
' This function imports Excel spreadsheet listed in tblImportExcel after emptying the table.
' It gets the spreadsheet name (FileName), the table name in Access (TableName), and the data
' range name in the Excel spreadsheet (Range) from the table called tblImportExcel.  You can
' have as many spreadsheets to import in this table as you want.  Just make sure you have the
' range named in Excel and you include all the data in that range.

    Dim rst As Recordset
    Dim strCurrentPath As String
    
    Set rst = CurrentDb.OpenRecordset("tblImportExcel")
    strCurrentPath = GetPath(CurrentDb.Name) & "\"
    
    Do Until rst.EOF
    
        'Empty the table
        modUtilities.EmptyTable rst!TableName
    
        If FileExist(strCurrentPath & rst!Filename) Then
            'Import Excel 2000 spreadsheet
            DoCmd.TransferSpreadsheet TransferType:=acImport, _
                                        SpreadsheetType:=acSpreadsheetTypeExcel9, _
                                        TableName:=rst!TableName, _
                                        Filename:=strCurrentPath & rst!Filename, _
                                        HasFieldNames:=False, _
                                        Range:=rst!RangeToImport
        Else
            MsgBox (rst!Filename & " does not exist.")
        End If
        rst.MoveNext
    Loop
    
    Set rst = Nothing
    'Stop
End Sub      'ImportExcel

thanks