I have a daily report created in Excel that I'd like to import into a table in Access using VBA. I will need to do some analysis and manipulation with the data but I can handle that part. The part I need help with is the following:
I'd like to specify a directory on my hard drive that contains roughly 30 excel files and need a script to do the following with it:
* Load the first excel file in ascending order, take all the records in the "Scheduled Rescheduled Missed" tab and load it into a table in Access.
* In the access table need one more field called "Data_Date" where the date of the excel data is recorded for each record. This date will need to be extracted from the Excel File Name
* Go to the next excel file and repeat the process until data from all excel files in that folder has been imported.
There is a sample of the data file attached for your reference.