VBA to Export Data from Excel to Access

[Matrix007]

Dear Experts

VBA code required to Export Data from Excel to Access

I have a table in excel with 5 columns that I want to export to an access database.

The data in excel is per below and all the 5 columns exist with the same heading names in the access database.
The table name in the database is “Key_Statistics”
The database locations is C:\Users\John\KPI_Test.accdb

I also want make sure that if there is a existing record for an ID then update, if there is a new record then ADD it to the table.
Note I am using MS Excel and Access 2016
Would appreciate your help with what VBA code can help me with this.


[TABLE="width: 522"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]product[/TD]
[TD]sales_revenue[/TD]
[TD]Cost[/TD]
[TD]Profit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PN1[/TD]
[TD]pens[/TD]
[TD] 200[/TD]
[TD] 150[/TD]
[TD] 50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]LP2[/TD]
[TD]laptops[/TD]
[TD] 500[/TD]
[TD] 375[/TD]
[TD] 125[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PR3[/TD]
[TD]printers[/TD]
[TD] 200[/TD]
[TD] 155[/TD]
[TD] 45[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

I would do this all your from the Access side, like this:
1. Link your Excel data to a temporary Access table
2. Do a matched query between this temporary Access table and your final/permanent Access table. Update all the fields on the matching records (via an "Update Query").
3. Do an unmatched query between this temporary Access table and your final/permanent Access table. Add all the unmatched records (via an "Append Query").

 

[Matrix007]

Hi, thank you for your reply.

The reason I need a vba code is so that I can deploy that via an ad in that I already use. I have over 50 workbooks I need to get data from.

Therefore I need a vba code, please.

 

[Joe4]

I think it is much easier to control this kind fo stuff from the destination program (Access) than the origination program (Excel).
Personally, I think that I would just have the Excel VBA code export all the data to tab-delimited text files in a specific location.
Then, I would program an Access program to run at whatever times you want to import all that data to a temporary holding table in Access, and perform steps 2 and 3 I mentioned above.

But that is just my preference. I prefer to let Excel control the Excel things, and Access control the Access things, instead of trying to control one from the other. It can be a bit complex to do that. I never do that myself. The most I have done trying to control one from the other is have Access VBA call and run Excel Macros/VBA.

If you are interested in pursuing my idea let me know, and I can probably help you out with that. But if you want to try to use Excel VBA to control and do things in Access, I am not your guy for that.

 

[Matrix007]

Hi
I am happy to try your solution, so thank you in advance.
1. How do I export data to a central location? Are we exporting to one central file? or is the idea that the central location will hold multiple text files? As mentioned earlier, I have over 50 workbooks out there being used by multiple people. I have been asking them to manually update the data in a central location but as you can imagine it is a nightmare. I want to automate this task. The workbooks and users will continue to grow. The workbook template is locked with VBA codes for other tasks. I want something that is scalable.

 

[Joe4]

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.

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.

 

[Matrix007]

Thank you. I will go through and follow your steps.
I am very new/basic to VBA, so I will ask if there are any questions.

 

[Joe4]

You are welcome. In case it helps, here is how I would probably approach the Access side of things, in more detail.

First, create your temporary table, append query, and update query.
The structure temporary table will really just be a copy of your final data table, with no data in it. If you make all the field names the same, it will make it easier.
If you manually go through the steps of importing a text file into it once, you can save the Import Specification so you can use it in your VBA code (see: https://support.office.com/en-ie/ar...fication-6b94e183-2b10-4333-a31a-001fe75321b5).
Then create a matched query between the temp and final table (to find the changed records), and change this to an Update Query (to update the records in the final table).
Then create unmatched query between the temp and final table (to find all the new records), and change this to an Append Query (to write the record to the final table).

Once you have all those objects created, then you can write the VBA code, which should:
1. Check your folder for any new files (if none, stop here)
2. If there are any files in the folder, move them to an Import folder
3. Loop through the folder and import all the files
4. Open the Update Query to run it
5. Open the Append Query to run it
6. Delete all the records from the temporary table
7. Move all the files from the Import folder to an Archive folder

A few notes:
The reason for moving the folders to an Import file instead of just importing directly from the folder is to account for the possibility that someone could be adding more files while this process is running. So we don't want to chance missing any because a file got added to the folder midway through the process.
Be sure to run the Update Query before the Append Query. Otherwise, if you run in the other order, the new records you just added will needlessly be picked up in the Update Query too, and updated (even though they do not need to be).