Take the 2-minute tour ×

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?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer


