VBA Import Code to Access Help

[Lankanzero]

Hi Everyone,

I am creating an import code for Access from Excel that takes every excel file in a folder, imports the designated Sheet (i.e only Sheet1) and within a designated area of space in the sheet(i.e Sheet1!A32:T1881) and import it to an access called Table called "Test1". This I have working.

In every Workbook I have a seperate sheet (i.e Sheet2) that only contains text name (I.e Company1) in Cell A1.

I am trying to get every line of excel that gets imported into the Access table to have a separate field "Company Name" for each record in the Import.

Private Sub Command0_Click()

Dim strFile As String
Dim strPath As String


DoCmd.SetWarnings False


' Set file directory for files to be imported
strPath = "X:\Trans\DISPATCH\Business Analysis - Dispatch\Liam\Files"
' Tell it to import all Excel files from the file directory
strFile = Dir(strPath & "*.xlsx*")


' Start loop
Do While strFile <> ""
' Import file
DoCmd.TransferSpreadsheet transfertype:=acImport, TableName:="Test1", FileName:=strPath & strFile, HasFieldNames:=True, Range:="Sheet1!A32:T1881"
' Loop to next file in directoryI
strFile = Dir
Loop


MsgBox "All data has been imported.", vbOKOnly


EDIT:
An example could be like if Sheet2 said Company1 then the import would look exactly like Sheet1 with just a column that for every row that was pulled from Sheet1 has an identifier that says Company1 in a separate column. If that makes sense. I'm just trying to identify which company each import belongs to.