Paul and everyone
Got this to work for myself, and Thanks to Paul and for everyone for the hints.
Code:
Function ImportExcelFiles()
' Replace with the actual path and filename
Dim FilePath As String, ImportFile As String
FilePath = "E:\Data\Documents"
ImportFile = FilePath & "\" & "TableData.xlsm"
' Replace with the correct Spreadsheet type
Dim ExcelType As Integer
ExcelType = 10 ' "acSpreadsheetTypeExcel8"
' Change this to reflect first row in EXCEL worksheet having field names or NOT
Dim FieldNames As Boolean: FieldNames = True
' Dim FieldNames As Boolean: FieldNames = False
'Get the cell ranges to appended to the Access Tables
Dim OpenDB As DAO.Database
Dim rstImports As DAO.Recordset
Set OpenDB = CurrentDB()
Dim CellRange As String: CellRange = "ImportTables" 'The Named cell range holding the Range/Tables
Dim TableName As String: TableName = "ImportTables" 'The Access table to be imported into (assumed empty)
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM " & TableName 'Empty the access table w/checking
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acImport, ExcelType, CellRange, ImportFile, FieldNames, TableName
Set rstImports = OpenDB.OpenRecordset(CellRange) 'Load that data into a Recordset
' For each record in the data set Import the range into the given Table
Do While Not rstImports.EOF
CellRange = rstImports("Range")
TableName = rstImports("Table")
DoCmd.TransferSpreadsheet acImport, ExcelType, TableName, ImportFile, FieldNames, CellRange
rstImports.MoveNext
Loop
End Function 'ImportExcelFiles
So this requires in the spreadsheet "TableData" to have a named range "ImportTables" and a matching Access Table "ImportTables" with two fields, Range and Table.
It will handle as many ranges/tables in the "ImportTables" range that you have, but note you must load them in an order that respects the dependencies within the tables
As is with no warranties