Print Page | Close Window

ExportToExcelCharts.mdb (intermediate)

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=349
Printed Date: 19 Mar 2024 at 3:16am
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com


Topic: ExportToExcelCharts.mdb (intermediate)
Posted By: Roger Carlson
Subject: ExportToExcelCharts.mdb (intermediate)
Date Posted: 01 Aug 2008 at 4:35am

ExportToExcelCharts.mdb

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

New
Access 2010 (.accdb) http://www.rogersaccesslibrary.com/forum/uploads/5/ExportToExcelCharts2010.zip" rel="nofollow - Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<




Replies:
Posted By: Roger Carlson
Date Posted: 25 May 2016 at 7:24am
Update: Add ACCDB example.

-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 - http://www.webwizforums.com
Copyright ©2001-2019 Web Wiz Ltd. - https://www.webwiz.net