PDA

View Full Version : Export single row from given sheet & append into existing MSAccess table.



henry l
02-15-2017, 09:39 AM
What I would like to do is add a macro in Excel that exports a given range(A2:S2) of information from a specific sheet called "Access" and append it to an MSAccess table called "Tbl-Form". Then return the new Access table auto number ID# into Excel as a msgbox with an OK button.

I'm not sure where to start.

The Excel file is filled out in the field by a user and emailed back to us.
There are multiple sheets in this Excel workbook and the file always has different names.
Depending on who opens the email and the file, the folder within Windows would be different as well.


The intial user fills out the first sheet called "Update Form" and then sends it back via email.
Once the email is opened, an additional sheet called "Entry" has data input by myself or a co-worker.


Right now, on this "Entry" sheet, I have a button set up with a macro that pushes data from both the "Update Form" and "Entry" sheets onto the "Access" sheet. This works great and the formatting is perfect.


The Excel "Access" sheet has field names at A1:S1 and the data populating it is at A2:S2.
The Excel field names match the MSAccess table field names as well.


I've an MSAccess file sitting on a server, not on the local computer, with a table in it called "Tbl-Form".


What I would like to do is add a macro, that, after using my current population macro, then exports the Excel "Access" sheet information row (A2:S2) to the MSAccess "Tbl-Form" table and then returns the MSAccess table ID # as a msgbox with an OK button.


I am not certain what more you might need, but I would be happy to answer any questions you might have.


Thank you in advance!