One of the strengths of the Microsoft Office suite is the
ability for its component parts to communicate between themselves. It is
particularly useful to communicate between Access and Excel because while
Access is superior at storing data, Excel is superior at manipulating it. For
example, I am often asked if it's possible to send data from Access to
formatted cells in Excel and create a chart based on it.
This problem can be solved by extensive use of Office
Automation, but many people find this prospect daunting. Office Automation
through VBA (Visual Basic for Applications) is an extremely powerful but
complicated method. I discussed this method in my post: http://rogersaccessblog.blogspot.com/2016/05/how-do-i-export-access-data-to-excel_23.html" rel="nofollow - How do I export Access
data to Excel - Part 3 .
But there are other methods, like the Access TransferSpreadsheet
method, that are easier to use, but far more limited. http://rogersaccessblog.blogspot.com/2016/05/how-do-i-export-access-data-to-excel_10.html" rel="nofollow - How do I export Access
data to Excel - Part 2
A Middle Ground
However, it's also possible to solve with a combination of
built-in features of both Access and Excel, that is, Excel templates, a tiny
bit of Office Automation, and the Access TransferSpreadsheet method. This
middle ground uses the strengths of both, and is both easy and flexible.
The TransferSpreadsheet method allows me to export a query or
table from Access to Excel. If the workbook does not exist, it will create one.
If the workbook does exist, it will create a new sheet in the workbook named
after the table or query. But if both the workbook and sheet already exist,
Access will over-write the data in the sheet. This is the critical feature.
Another feature I'll make use of is Excel's ability to link
cells from one sheet to another. This means I can link a chart on one worksheet
to another worksheet that holds the data. If I use the TransferSpreadsheet
method to export a query that overwrites the data in the data worksheet, my
chart will be updated automatically.
Lastly, I will use an Excel template to create a new Excel
workbook with pre-formatted cells and charts. An Excel template is a special
kind of workbook with a .xltx extension. When you open a template, it automatically
creates a new workbook with a .xlsx extension, leaving the template untouched.
These features, used in combination with a small amount of
Office Automation, give me all the tools I need to accomplish the task.
Overview ....
To read more, download the sample
Download
Access 2000 (.mdb): http://www.rogersaccesslibrary.com/forum/uploads/5/ExportToExcelCharts2k.zip" rel="nofollow -
uploads/5/ExportToExcelCharts2k.zip