?
Solved

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

Posted on 2009-07-02
6
Medium Priority
?
797 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 20

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 20

Accepted Solution

by:
darbid73 earned 1000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 20

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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