Import data from multiple .mdb to multiple sheets

Hi all,

I want to update data from multiple .mdb files to multiple existing sheets at the same cell location, in one workbook.
All the .mdb files have the exact same format and I named them the same, "Raw".
My intended result is to update/match the data with the same file name and sheet name, i.e. A.mdb to Sheet A, B.mdb to Sheet B and etc.
I have a named range Range("FileName") where I listed down all the source files, so that the macro will loop for each name in the list.

Below is the current code that I have:

Sub import_access()
    Dim sSQL    As String               
    Dim cnt     As ADODB.Connection    
    Dim rst     As ADODB.Recordset      
    Dim rngCell As Range
    Dim rngFileName As Range
    
    On Error Resume Next
    
    Set rngFileName = Range("FileName")           '/ File Name of source file .mdb
    
    For Each rngCell In rngFileName
    
        If rngCell <> "" Then
    
            sSQL = "SELECT START_TIME,ONCO,OAHT FROM [Raw]"
            Set cnt = New ADODB.Connection
            Set rst = New ADODB.Recordset
    
            With cnt
                .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
                "Data Source=" & "C:\Users\hyeap\Desktop\" & rngCell & ".mdb" & ";" & "Jet OLEDB"
                .Open
            End With
    
            rst.Open sSQL, cnt, adOpenDynamic, adLockPessimistic
            Sheets(rngCell).Range("A40").CopyFromRecordset rst

            'rst.Update
            rst.Close
            Set rst = Nothing
            cnt.Close
            Set cnt = Nothing
    
        End If
    
    Next rngCell
End Sub

I got an error and I think it's because the destination sheet name cannot be recognised (dim as range).
Appreciate if anyone can assist.

Thank you.

Hi TMS,

Thank you for your reply.

Never thought that a small trick will do the magic!

Thanks!