Experts:
I need some assistance with reworking some data handling/management (via VBA routines in MS-Access).
Before going into the specifics for the VBA, please allow me to provide some high-level background first.
Existing process (see example file "VBA Code from Another File.txt" for details):
================================================== ===============================
1. I have a folder with 38 MS-Excel files (e.g., "AccidentalDischarge.xls", "AlcoholRelatedIncident.xls", etc.) on my computer. It is noteworthy to point out there are 13 different field/column structures for these 38 Excel files.
2. Depending on business/analysis activities, the 38 Excel files are updated at different frequencies. In other words, some files are up-to-date with current data while other Excel files may not have been updated in some time.
3. At this time, in MS-Access, I bring up a form with a listbox containing several "Steps".
4. When clicking on the listbox (e.g., "Step 1"), the following actions are perform through VBA:
4a. Drop all records from a MASTER table (i.e., "000_tblRawData").
4b. Drop all records from 38 TEMP tables (e.g., "tblRawData_AccidentalDischarge", "tblRawData_AlcoholRelatedIncident", etc.).
4c. Run 38 *import routines" which import all of the data from the 38 Excel files into the 38 TEMP tables (regardless whether or not updates to the Excel files were made).
4d. Run 38 *append queries" which add all records/data from the 38 TEMP tables (again, each of the have different field structures) into the MASTER table which accommodates the total # of different fields from the 38 Excel files.
However, I would prefer to only run the IMPORT ROUTINES and APPEND QUERIES for those Excel files which were recently updated. In essence, if this week's analysis requires me to only review 3 out 38 "categories", I don't need to use up CPU time for importing the other 35 EXCEL files (each can have thousands of records).
// BREAK BREAK //
To address a more efficient process, I have created a separate Access file "Multi Select" (see attached). This file is for testing only at this time. Here's what the file does:
1. Upon clicking on the form "frmSelectCategories", the user can select any/all of the available categories. In this example, I have only included 7 categories... but again, in actually, it will be 38 categories.
2. Once a category selection has been made, I click on "Run Query" which updates the append query and then appends all matching records from "000_tblRawData" into "001_tblRawData_SelectedCategories"
// NOW, HERE'S WHAT I NEED SOME HELP WITH... //
- I want to apply the concept of the Multi Select database (i.e., choosing a subset of categories from the listbox) and modify the existing process (see VBA in attached text file).
- So, again, if this week's analysis requires only the assessment of a few categories only, then the selection of those categories will execute, e.g., only 3 import routines and 3 append queries. Therefore, I reduce CPU time by only
importing the Excel files needed at that time (vs. all Excel files).
I hope the above was not too confusing... any assistance with modifying the **VBA code in the TEXT file** would be greatly appreciate.
Thank you,
EEH