Adding file name when importing multiple excel files

Elczula

New Member
Joined
Mar 19, 2021
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016
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

VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,089
Office Version
  1. 365
Platform
  1. Windows
Can you give an example of what strFile contains? It might be as simple as using Mid function.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top