I am working with a cross-functional team and responsible for consolidating information from multiple users.
All the users drop their files in a specific folder with agreed upon files names and tab names. Not all the users provide same information(i.e. is some provide sales related info and some provide production information)
Currently I get around 30 excel files which I have to load into access in different tables based on for file and tab name. I need help to write a code to do the following.
Step1: read file (xlsx) names and their tab names and check each for now of row and columns having data. Store this information in a table (Say: TBL_File_List)
<tbody>
</tbody>
Step2: Import each tab from each file per table TBL_File_List and use logic (based on filename and tab name) to import it to specific destination tables and also time stamp each record
Step3: Rename each file, example fileA as fileA_20121013.xlsx.
Step4: Move all the files to Archive folder
Though I able to do this partly by getting some code snippets from internet but it is still not smooth. So I am looking for a clean new approach to do this task especially part 1 which what I am struggling on.
I look forward to help from talented people on this group.
Thanks
SKV
All the users drop their files in a specific folder with agreed upon files names and tab names. Not all the users provide same information(i.e. is some provide sales related info and some provide production information)
Currently I get around 30 excel files which I have to load into access in different tables based on for file and tab name. I need help to write a code to do the following.
Step1: read file (xlsx) names and their tab names and check each for now of row and columns having data. Store this information in a table (Say: TBL_File_List)
File Name | TabName | Rows | Columns | TimeStamp |
FileA | Mtl_list | 20 | 12 | 2012-10-13 1pm |
FileA | BOM | 37 | 12 | 2012-10-13 1pm |
File2 | Sales | 1000 | 20 | 2012-10-13 2pm |
<tbody>
</tbody>
Step2: Import each tab from each file per table TBL_File_List and use logic (based on filename and tab name) to import it to specific destination tables and also time stamp each record
Step3: Rename each file, example fileA as fileA_20121013.xlsx.
Step4: Move all the files to Archive folder
Though I able to do this partly by getting some code snippets from internet but it is still not smooth. So I am looking for a clean new approach to do this task especially part 1 which what I am struggling on.
I look forward to help from talented people on this group.
Thanks
SKV