Create a graph in a Word doc from Access based on access table data

Posted on 2009-07-02
Last Modified: 2012-05-07
I have a graph/chart problem where I cannot find the proper, clean solution so far. Here is the setup:
- From Access, I create a Word doc in which there are a number of merge fields which I replace with data from tables in Access. This works fine
- Now in the Word doc, I'd like to add a chart/graph based on data on a table in Access

All the solutions i found either show how to do a graph on an Access report, or in an Excel sheet, and then copy it in Word. What I would like however, is:
- either create the graph directly in Word from my Access VBA code, and feed it the Access table data. I have however no idea how that should be done. it seems from the couple of macros I recorded that a graph in Word is an inline object fed from a file, which for me is not a "clean" solution
- or create the graph first in Access with the table data, and copy/paste it in Word. But here again, I found only example how to create a graph on an Access report as "base"

- Isn't it possible to create directly a graph object in Access, WITHOUT any "base" object such as a report, and then to copy/paste it to Word ?
- Is it possible to feed data to an inline MS graph object in Word without creating the graph first as file ?
 - Is it necessary/best to go over the intermediary step of an Excel spreadsheet ?

In other words, what do you reckon the best solution is for my setup ?

Thanks for your help.
Question by:bthouin
  • 3
  • 3
LVL 20

Expert Comment

ID: 24763033
I was sitting right in your position about 6 months ago.  

I did not even try to do it in Word.

I simply open an excel template (keeping it invisible) create the graph, copy it to a place in word and then destroy the Excel object.  Unless the user is looking at Task Manager during this process he would have no idea that Excel was involved.

'start wour word code here
'--- open a workbook
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
      'Excel is not running; creating a Excel object
      Set objXL = CreateObject("Excel.Application")
   End If
'I use a template for my graph
Set objWkb = objXL.Workbooks.Add(strTemplate)
'for debugging this would make it visible
'objXL.Visible = True
'your specific Excel code here
'select your chart you need to have set your worksheet as an object and in my case I only have on chart which is (1)
YOURSHEETOBJECT.ChartObjects(1).Chart.Select.Chart.CopyPicture Appearance:=xlScreen, size:=xlScreen, Format:=xlPicture
'go back to word
With objWord.Selection 'i set a bookmakr called chart
    .GoTo what:=wdGoToBookmark, Name:="chart"
    .PasteSpecial DataType:=wdPasteMetafilePicture
End With
'fix it up for my document
objWord.ActiveDocument.Shapes("Picture 2").Select

Open in new window


Author Comment

ID: 25006237

Sorry, doesn't work. I get an error on the YOURSHEETOBJECT.ChartObjects(1).Chart.Select.
Error is: Select method of Chart class failed
I have defined my sheet as:
    Dim eSheetObj As Object
    Set eSheetObj = eWbk.Sheets(1)
where eWbk is my workbook (dim eWbk as excel.workbook)

The error happens on:
eSheetObj.ChartObjects("Chart 1").Chart.Select

If I take away the .Chart in that statement, it executes, but then the next fails, regardless how I fiddle with it (removing .Chart, or .Chart.Select).

What do I do wrong ?
LVL 20

Accepted Solution

darbid73 earned 250 total points
ID: 25007138
That is strange but thanks cause I am using this as well and at some stage am going to get an error.  I will have to check why I do not.  I think that I have made a mistake in copying the code for you as I took out bits in and around this.

in any case I you do not need the select.  This below is enough to get the chart copied. Where Sheet 2 is the name of your sheet and Chart 1 is the name of your chart.

Application.Worksheets("Sheet2").ChartObjects("Chart 1").Chart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

You can now shorten this based on what objects you have inialized.

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 25014243

Great ! It works now, thanks a lot.

But I have 2 more questions:

1) How do I now after the paste in Word what's the Shape name or Id of the pasted Chart. By going through the Shapes collection, I found out by guessing that it was id 3 (because it was called "Picture x", rather than "Rectangle y"), but that's not acceptable in code, I could have more shapes in the document I'm pasting in. I need the Id or name to do "Shapes("<name>" or Id).Select" call

2) more importantly, how do I shrink the pasted chart. I am pasting in a table cell, and I want that cell to keep its size. Currently, the ConvertToInlineShape call sets it within the cell, but blows up the dimensions of that cell.
LVL 20

Expert Comment

ID: 25014460
here is how I deal with it.

The great thing about word is that the macro recorder works great.

So what i would suggest is for these really fine details or special requirements to get it just right is to Cut and copy the Chart to your word doc.  Then turn on the macro recorder and make your changes to the chart in word to get it just the way you want.  Then review the macro recorded.

'Paste into Word as metafile image
With objWord.Selection
    .GoTo What:=wdGoToBookmark, Name:="chart"
    '.PasteSpecial DataType:=wdPasteEnhancedMetafile
    .PasteSpecial DataType:=wdPasteMetafilePicture
End With
objWord.ActiveDocument.Shapes("Picture 2").Select

Open in new window


Author Comment

ID: 25035505
Sorry, obviously I was not clear enough.

I use the macro recorder quite often, but this time it won't help me. I am processing (using VBA code) from an Access DB data in the DB and put the data in Word documents. The data can be of various type, and for each type I have a different Word template. My VBA code extract the type of data, opens automatically the corresponding template, copies it to the final document, goes though all defined merge fields in the template and replaces them with data, or with a chart, when the merge field tells the code to generate a chart from a set of given data. My templates are sufficiently different so that the code knows neither if the chart is located in a table cell or not, and if there are only one chart or more in the document. This means that the code must find out in a very generic way:
- is it pasting the current chart in a cell (for the moment all my charts are held in table cells) ?
- what are the dimensions of the cell ?
and therefore shrink the chart automatically to the dimensions of the cell.
I can't use the macro recorder for that, because it cannot simulate the code pasting a chart from Excel into Word just where the merge field is in a generic way.
Moreover, after the code does the final conversion to an inline shape, I still have to shrink the chart to the original table cell dimensions, and I have no idea what reference/name/id to use to refer to the chart just pasted. Your code stops after the conversion.

For the moment, I've just done a workaround by shrinking the chart in the Excel template so that it fits in the table cell, but all the problems remain, especially the reference to which Shape should be selected for converting to inline shape.

Thanks for any hints about how to do all this in a generic way.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question