Excel sheet into multiple tables using VBA

[ElcoyotldeAztlan]

Hello everyone,

I have been trying to see if there is a way to use one excel sheet and import individual columns into individual tables

it appears I can use a VBA code from a youtube video but its just importing the excel sheet into one table. The way how the creator is describing the code, I can recode the VBA and select parts of the excel sheet into the table I want, maybe i'm wrong?

The code is listed in the description and you have the option of downloading the work file, here is the import code

Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)
On Error GoTo BadFormat
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True
Exit Sub

BadFormat:
MsgBox "The file you tried to import was not an Excel spreadsheet."
End Sub


I would assume I would change TableName to the name of my desired table or/and use the Range code as well

Any suggestions?
Thanks!

 

[Ranman256]

or ,you can link the external excel file as a table,
then build N append queries to import the various columns. 1 qry / table.
put all queries into a macro,
run the macro.

 

[ElcoyotldeAztlan]

Wow that really solve my issue Thanks,