A colleague of mine organizes her data using MSAccess. she outputs the table and pastes it into an excel file (same headings/ columns,,,,). The excel sheet then does some calcs and outputs a summary sheet.

Is there a way to automate this process?


There are two ways to solve this: 1) Create a report in Access that does everything that Excel does. This is kinda of a no-brainer so I'll assume that there is some reason why this isn't done.

The other way is to make an ODBC connection from Excel to the mdb file. This will allow Excel to directly use the Access table (real time if required). I do this because it is easier to format a report in Excel vs. Access.

This Microsoft link has and overview http://office.microsoft.com/en-us/excel/HP052747511033.aspx

Using this method requires that you setup the database as a datasource. That process is not difficult.

Got the the Data / Import External Data / New Database Query menu item and select New Data Source. Name the 'data source' Select the Microsoft Access (*.mdb) driver and click Connect. Hit the Select button to pick your database and click OK. Then pick your table within the Database. You will then be asked which columns to include. You can pick them all. Hit next, next and Finish. Pick a spot for the upper left corner of the data and you're done.

| improve this answer | |

You can create a query that can be inserted into a worksheet pretty easily in vba:

If you want to use a template:

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = False

Set xlWorkbook = xlApp.Workbooks.Open(sTemplate_Full_Folder_Path_and_Template_File_Name, False, xlReadWrite)

Open a recordset:

rs.Open Some_Query_Name , CurrentProject.Connection, adOpenKeyset, adLockReadOnly

Place the results of the recordset in Excel. I like using a name for the Cell.

xlSheet.Range("SomeCellNameInExcel).CopyFromRecordset rs

You can put the headers to the columns in the Template

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.