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