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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.Sign up to join this community
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.
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.
You can put the headers to the columns in the Template