I am creating an Access database that will be created from several Excel workbooks. The final results will need to be in both an Access report, and an Excel worksheet. The database will be online, likely through a remote desktop solution.
I can think of 2 methods in which this could work:
1) I link the Excel workbooks to the Access database. I take the input values and make a form in Access to be filled out. The form is filled out and spits the values back to Excel. Excel performs its calculated formulas. Access then grabs the resulting values and fills out a report with them.
However, I am unsure if this is possible in terms of returning values back to Excel, then grabbing the results? Any guidance here would be greatly appreciated!
2) I recreate the work of the Excel workbooks in an Access database. I then use VBA to export the results to an Excel template.
I am not sure how to do this, but I have found this page: http://www.databasejournal.com/featu...a-To-Excel.htm
Unfortunately, whenever I run the MDB it gives me an error.
I would think the 1st method would be much easier if possible. If it helps, I will be using Access 2007.