Adding file name when importing multiple excel files

[Elczula]

Hello, I am new to Access and VBA, so I apologise in advance for any misunderstanding.

I have some code that imports from multiple excel files into three different tables in Access. Not all excel files have the same worksheets in them, so I am only importing from specific worksheets which are always consistently named as Certified, Audited or Returned.

The code is working as it should, however I want to add in some additional code to add the excel file name into a field in each table into a field called 'FileName'. I have been struggling to do this. I have found some code online but couldn't get any of it to work. Any ideas on how I could do this?

Below is my current code which I found online here: EXCEL Import

Thank you

Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
Dim strWorksheets(1 To 3) As String

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)
Dim strTables(1 To 3) As String

' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strWorksheets(1) = "Certified"
strWorksheets(2) = "Audited"
strWorksheets(3) = "Returned"

' Replace generic table names with the real table names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strTables(1) = "tbl_Import_Certified"
strTables(2) = "tbl_Import_Audited"
strTables(3) = "tbl_Import_Returned"

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "K:\ExcelFiles\"

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 3
On Error Resume Next
      strFile = Dir(strPath & "*.xlsx")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(intWorksheets) & "!A:R"
            strFile = Dir()
      Loop

Next intWorksheets

MsgBox "All data has been imported.", vbOKOnly

 

[silentwolf]

Hello, I am new to Access and VBA, so I apologise in advance for any misunderstanding.

I have some code that imports from multiple excel files into three different tables in Access. Not all excel files have the same worksheets in them, so I am only importing from specific worksheets which are always consistently named as Certified, Audited or Returned.

The code is working as it should, however I want to add in some additional code to add the excel file name into a field in each table into a field called 'FileName'. I have been struggling to do this. I have found some code online but couldn't get any of it to work. Any ideas on how I could do this?

Below is my current code which I found online here: EXCEL Import

Thank you

Dim strPathFile As String, strFile As String, strPath As String
Dim blnHasFieldNames As Boolean
Dim intWorksheets As Integer

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
Dim strWorksheets(1 To 3) As String

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file (this code assumes that each worksheet
' with the same name is being imported into a separate table
' for that specific worksheet name)
Dim strTables(1 To 3) As String

' Replace generic worksheet names with the real worksheet names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strWorksheets(1) = "Certified"
strWorksheets(2) = "Audited"
strWorksheets(3) = "Returned"

' Replace generic table names with the real table names;
' add / delete code lines so that there is one code line for
' each worksheet that is to be imported from each workbook file
strTables(1) = "tbl_Import_Certified"
strTables(2) = "tbl_Import_Audited"
strTables(3) = "tbl_Import_Returned"

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "K:\ExcelFiles\"

' Replace 3 with the number of worksheets to be imported
' from each EXCEL file
For intWorksheets = 1 To 3
On Error Resume Next
      strFile = Dir(strPath & "*.xlsx")
      Do While Len(strFile) > 0
            strPathFile = strPath & strFile
            DoCmd.TransferSpreadsheet acImport, _
                  acSpreadsheetTypeExcel9, strTables(intWorksheets), _
                  strPathFile, blnHasFieldNames, _
                  strWorksheets(intWorksheets) & "!A:R"
            strFile = Dir()
      Loop

Next intWorksheets

MsgBox "All data has been imported.", vbOKOnly

Hello,

one way would be to achieve those kind of things with a property let and get statements.
So you puting the file location into a property and when you need that property you take it out and place it into your desired field.

The file path and file name you have already so put those into the property and then out again.

HTH

If you are not sure how properties in vba work have a look into those I use them quite a bit they are pretty handy little things.

 

[Micron]

Can you give an example of what strFile contains? It might be as simple as using Mid function.