asked on

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

Hi
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"

Questions:
- 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.
Bernard

darbid73

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")
      Err.Clear
   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
YOURSHEETOBJECT.ChartObjects(1).Chart.Select.Chart.CopyPicture Appearance:=xlScreen, size:=xlScreen, Format:=xlPicture
 
'go back to word
objWord.Activate
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
objWord.Selection.ShapeRange.ConvertToInlineShape

Open in new window

Bernard Thouin

ASKER

Hi

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 ?

ASKER CERTIFIED SOLUTION

darbid73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bernard Thouin

ASKER

Hi

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.

darbid73

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
objWord.Activate
With objWord.Selection
    .GoTo What:=wdGoToBookmark, Name:="chart"
    '.PasteSpecial DataType:=wdPasteEnhancedMetafile
    .PasteSpecial DataType:=wdPasteMetafilePicture
End With
 
objWord.ActiveDocument.Shapes("Picture 2").Select
objWord.Selection.ShapeRange.ConvertToInlineShape

Open in new window

Bernard Thouin

ASKER

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.