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/ms...ta-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.

the answer to both scenarios depends on the person writing the DB.They would also lean toward their strength whether it be Access or Excel.

You can import data from Excel to an access table, add more data via a form, run a module that performs all the same things that you would in excel and then export it to Excel.

I have situations where I have an Excel template that contains a few macros that manipulate columns and adds data based on the values of other columns. I send it an export from Access then call the
"start" macro from VBA in Access.

So, I guess the answer is up to you. We can certainly help you if you stumble on your way.

I agree with Bob, the biggest question here seems to be which platform(s) you're more comfortable with, as there are many ways to accomplish all of these things. Generally speaking, here are a few of my thoughts from my own personal experience:

1. I hate linking excel sheets to access databases. Format issues CONSTANTLY plague the situation, and if that doesn't get you, link breakage (due to file removal, renaming, deletion, etc) or version issues can arise. Any of these totally kills the process if it can't be resolved, and continues to require more and more "band-aid coding" to cover up the problem.

2. It sounds like possibly you could benefit from using a Userform in excel. You don't need to use Access to have them fill out a form, then put data back to Excel to do calculations, then back to Access. Unless you absolutely need an "access report", you can do all of this in Excel. Using just one platform for the actual designed project might make your life simpler in so many ways.

Here are 2 ideas:

1. Create a data-entry type of userform in Excel, continue using that same Excel workbook to do any necessary calculations or data manipulation, then use VBA to export the report from Excel to any options the user needs: Excel report, PDF, etc. (again, unless you absolutely NEED an access report...I've never run into a user yet that requires or even would realize any difference, functionally speaking, from an Access report when displayed on screen, versus some other kind of report when displayed on screen).

2. If you really need to end up with a conventional access object Report in the end, then still you can accomplish it with less of the back-and-forth stuff. You might do a) Data entry form in excel, b) then all excel calculations, then c) excel vba to export all that data to a text file, then d) link the text file to a simple access TABLE, e) display an access report based on table.

When you get started and have specific questions about it we are happy to help.