I would create a macro in the Excel file that saves the file to some designated path that you have set that they have access too. To ensure a unique filename, you can use their environment username as part of the file name (see:
https://www.mrexcel.com/forum/excel...erprofile-filename-when-saving-using-vba.html). I would probably add a date/timestamp piece to the file name as well, like shown in that thread (if they may be doing more than one file per user per day, add a time piece as well, i.e.
Code:
Environ("Username") & Format(Date, "yyyymmddhhmmss")
I would recommend saving as tab-delimited text files into of Excel files, because importing Excel files in Access can be problematic, as you cannot tell Access the data type and format of each field, it tries to "guess", and if it guesses wrong, you will get errors. In importing text files, you can create an Import Specification where you tell Access EXACTLY the data type and format of each field. It just works better that way.
Then, on the Access side, you can create a macro that runs automatically whenever the database is opened (if you name the Macro AUTOEXEC). Or you can use a Startup form, and have some VBA code run on the Load event of that form. I would have that VBA code look for any new files in our folder, and if there are any, import all of them, then move them to an Archive folder. Then, it can process the queries to add the records to the final table. When finished, it should clear the temporary holding table, in anticipation of next time.
We often use Windows Scheduler to open the Access database at specific times of the day, so that it runs automatically and imports the data. Just be sure if you do this, that you have that macro/VBA code close the database when done. If you have need to go into the Access database to work it in manually, run reports, etc, you may actually want to split the database into a back-end with multiple front ends (
https://support.office.com/en-gb/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc). This is something you should do anyway if you may have multiple people in the Access database at the same time. Basically, the back-end just holds your final data tables. The front-end simply links to the data tables, and has everything else (Queries, Forms, Reports, VBA code). You can have multiple front-ends, all linking to the same data. So you could have the automated front-end, that does all the data importing and VBA code. And then you can have a separate front-end for users to work in, if need be (note that each user should have their own copy of this front end).
One last piece of advice. I don't know how comfortable you are writing VBA code for Excel and Access. Excel has a Macro Recorder, which, if you turn on, will record the manual actions you take. Then you can view the VBA code it created. This is often a great tool to get started or get little snippets of VBA code. So, if you wanted to see what the command to export an Excel file to a Tab-Delimited Text file is, you simply turn on the Macro Recorder, and record yourself performing that task manually, stopping the Macro Recorder, and viewing the resulting code.
Access does not have a Macro Recorder, but it does have Macros. You can do things like Open/Run Queries, import/export data, shut off warnings, run SQL code, etc. It isn't dynamic and you cannot do loops, but if you find an action that you want to do, and you want to see what the VBA code looks like, you can create that action in a Macro, and then use the "Convert Macros to Visual Basic" command, which shows you what that code looks like. So it sort of works like Excel's macro recorder, in that you can see what the VBA code for certain actions looks like, and you can build off of that.
Hope that gets you started! Feel free to post back with questions.
