Exporting 7 excel columns to an Access Table using Excel VBA

[playaller]

Hello all. I' have an excel file that from a sheet DupCheck Prep,

• I copy 7 columns (A through G) of data (of any number of rows) to,
• an Access table tbl_InputData and then,
• I run the access macro called Mac_RunDupCheck. I do this to check our database for records from this sheet that already exist.
• Once complete the results show in a access table labeled as Tbl_DupCheckResults
• The results span across 50+ columns of data which are pasted back into the excel workbook in a sheet called DupCheck Results

I'd like to automate as much as this as possible using excels VBA. I'll simplify and re-summarize this up below if anyone can help! Thanks so much!

Step 1.
Excel Export to Access
Sheet Name: DupCheck Prep
Action: Columns (A,B,C,D,E,F,G) copied to Access Table below

Step 2.
Access Import
DB Name: DupChecker 2016 v4.0
Access Table: tbl_InputData
tbl_InputData Field Columns (7): (SeqNum,InputName,InputAddress,InputAddress2,InputCity,InputState,InputZip)
(tbl_InputData can remain open or closed from view)

Step 3.
Run Access Macro
Macro Name: mac_RunDupCheck

Step 4.
Access Export to Excel
Access results table name: tbl_DupCheckResults
Export to Excel sheet name: DupCheck Results


That's it! I really appreciate any help getting as far into the steps as you can get. Thanks!!

[alansidman]

Here is an Access Template in .mdb format that I created awhile ago for importing spreadsheets into Access. See if this will get you started.

[playaller]

Thanks Alan,

I definitely get where that template is going and thanks for sharing! The only thing is that it seems to function starting from the Access side of things and I need a 1 click solution started from the excel side even if it means importing the excel data into the access table and stopping at that point or continuing to the next step by calling the access macro and stopping at that point as users will be running other macros from within Excel in addition to this one requested.

[alansidman]

I usually work from the Access side when doing this type of exercise. I did find what may work for you however.

https://www.exceltip.com/import-and-...oft-excel.html

Once the data is in the table, you can build your query in Access and automatically using the Docmd.transferspreadsheet function run the query and export it back to Excel. It is one continuous step.

Have you built your Access query yet?

[playaller]

Thanks i'll look at this and let you know.

Yes the Access querys and tables are already built and run fine one their own