Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Import multiple excel files and include file name
- Thread starter MITTENCAT
- Start date
1. You need to review how to use this site properly. I looked at your profile. You've asked questions, but never really let folks know what did or did not work. If you did, I certainly could not tell.
2. If you're using Access, there are MS Access specific forums of which you're aware, due to your user profile showing you've asked questions there.
3. This is most likely going to be done via VBA, so I'd specifically recommend forum705
That said, the general idea of what you want to do is:
1. Loop through all files in a folder (I'm assuming the files are in one place): Usually easiest way to do this is with the FileSystemObject.
2. For each file, check if is an Excel file: easiest way, I think, is to check the file extension.
3. Then if they are the exact same format, with little to no irregularity, you can use the TransferSpreadsheet command to import the data from each workbook/worksheet.
I'd add that if this is a routine thing, you definitely spend some time adding some error checking, b/c eventually something will not be correct. I'd check the file name, I'd check at least some values in the data before importing to make sure an invalid file with the proper file name didn't get dumped in. And perhaps you might want to do some checking around filtering out possible duplicate data.
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
2. If you're using Access, there are MS Access specific forums of which you're aware, due to your user profile showing you've asked questions there.
3. This is most likely going to be done via VBA, so I'd specifically recommend forum705
That said, the general idea of what you want to do is:
1. Loop through all files in a folder (I'm assuming the files are in one place): Usually easiest way to do this is with the FileSystemObject.
2. For each file, check if is an Excel file: easiest way, I think, is to check the file extension.
3. Then if they are the exact same format, with little to no irregularity, you can use the TransferSpreadsheet command to import the data from each workbook/worksheet.
I'd add that if this is a routine thing, you definitely spend some time adding some error checking, b/c eventually something will not be correct. I'd check the file name, I'd check at least some values in the data before importing to make sure an invalid file with the proper file name didn't get dumped in. And perhaps you might want to do some checking around filtering out possible duplicate data.
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57
Excel 2016 or 2013 with power query add-in: first consolidate your data in new workbook, grab data from files in folder, add column with file names. Next import single file to access. Examples:
combo
combo
- Thread starter
- #5
- Status