Microsoft Access 掲示板

こんな感じでどうですか。

Public Sub test()
    
    Dim excelBookName As String
    Dim Sheets As String
    Dim strRange As String
    excelBookName = "Book1.xlsm"
    Sheets = "data,aaa,bbb,ccc" 'カンマ区切りで対象シート名を記述
    strRange = "!A7:I"

    Dim myDir As String, excelPath As String
    myDir = Application.CurrentProject.Path 'データベースファイルのパス
    excelPath = myDir & "\" & excelBookName

    Dim sheet
    For Each sheet In Split(Sheets, ",")
        DoCmd.TransferSpreadsheet acImport, _
                              acSpreadsheetTypeExcel12Xml, "T_2022", excelPath, , sheet & strRange
    Next

End Sub