Roger's Access Library Homepage
Forum Home Forum Home > Roger's Access Library > Roger's Download Samples
  New Posts New Posts RSS Feed - ExportToExcelCharts.mdb (intermediate)
  FAQ FAQ  Forum Search   Events   Register Register  Login Login


Please consider donating $1 per sample downloaded, (find out why here)

ExportToExcelCharts.mdb (intermediate)

 Post Reply Post Reply
Author
Message
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 249
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Topic: ExportToExcelCharts.mdb (intermediate)
    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: 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. 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):
uploads/5/ExportToExcelCharts2k.zip

New
Access 2010 (.accdb) uploads/5/ExportToExcelCharts2010.zip

Consider donating $1 per sample downloaded, find out why here.<
Back to Top
Sponsored Links


Back to Top
Roger Carlson View Drop Down
Microsoft MVP
Microsoft MVP


Joined: 20 Feb 2008
Status: Offline
Points: 249
Post Options Post Options   Thanks (0) Thanks(0)   Quote Roger Carlson Quote  Post ReplyReply Direct Link To This Post Posted: 25 May 2016 at 7:24am
Update: Add ACCDB example.
Consider donating $1 per sample downloaded, find out why here.<
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down