Solved

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

Posted on 2009-07-02
6
776 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:bthouin
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:darbid73
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")

      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

0
 
LVL 1

Author Comment

by:bthouin
ID: 25006237
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 ?
0
 
LVL 19

Accepted Solution

by:
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.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:bthouin
ID: 25014243
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.
0
 
LVL 19

Expert Comment

by:darbid73
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

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

0
 
LVL 1

Author Comment

by:bthouin
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.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

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…
Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now