Which Export to Excel Method is Simpler to Implement?

[Heatshiver]

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/features/msaccess/article.php/3563671/Export-Data-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.

 

[Trevor G]

I would consider using the Docmd command to transfer into Excel from your database or run the code to transfer it via the Excel workbook. If you are running formula in the Excel before transfering it back to access consider using Macros around the formula as this can simulate all actions and then upload back to the database.

So I suppose the question is, what is your knowledge like around VBA, are you OK with recording macros in Excel? Have you look to convert macros in Access into VBA to get code for things like Docmd.TransferSpreadsheet?