The objective is to extract data from a number of excel files and to load it into a table in MS Access.
The excel files are stored in a series of sub-directories of a particular network directory.
There may be other files in the directories.
The *.xlsm (macro enabled excel files) files that we need have a named range (table) which contains the data to be extracted.
The Access code needs to….
1) Search through the sub-folders to locate all the excel files that have this named range
2) Extract that range and load it into a table in Microsoft access
3) The name and the location of the excel files need to be recorded
some of the files may be being used. It would be useful to have a record of this.
A number of different users will be running the code in access, from a shared application.
Nice to have: The possibility to schedule this refresh
1) The excel templates (versions of the same master template) are being updated by a number of users (on the network).
2) This data is consolidated into the access (2007) database [this is what the code is needed for]
3) A couple of queries will be run to process the data
4) The result is pulled into an excel (MS Query) workbook for reporting
It would be preferable if the process administrator could do everything from that excel workbook
This can be done in MS Access vba or as a vb application which can be run from excel (or scheduled)
The attached file has a process chart and an example of a data table with a range name (DataOut)
Please clarify how you would approach the project (e.g. vb of vba)
Clarification: The excel file names are not known up front. The code will need to find the excel templates with the specified named range and then import the data from them.