I am working on consolidating several Excel sheets into one table in Microsoft Access automatically. All of the the excel sheets exist in a folder located at C:\My Documents\Test_Files\. The file name for all of the excel sheets begin with "Document_Test" proceeded by the date the data was run. For example "Document_Test - 1.1.2010", "Document_Test - 1.15.20", "Document_Test - 2.9.2010" and so forth...
I want to be able to embedd a Visual Basic module in access that will be executed via a macro do pull ALL files beginning with "Document_Test" into one table in Access called "tblDocumentTest". However, the data for each sheet begins on row 2, column A and can go down as far as row 30,000 to Column BA. So the default range to pull from each sheet would be A2:BA30000.
If possible, as an extra, after pulling in the data from each excel sheet, could a new field called "FileName" be populated at the end of the data in the newly created "tblDoumentTest" that indicates the name of the file the data was pulled from. For example, when pulling in information from "Document_test - 1.1.2020", could a field be populated at the end of each row displayed "C:\My Documents\Test_files\Docum
ent_Test - 1.1.2010".
I have attached a sample excel sheet to display the data for which I want to pull in from each sheet.