• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

Edit Chart data points in Word pie chart from MS Access

I have created a MS Word document to use as a template. Included in the document is a pie chart that shows up as:
{ EMBED MSGraph.Chart8 \s } when I reveal the codes. If I then right click on that and select Chart Object>Edit I can manually edit the data in the table which will then be reflected in the pie chart.

I have also created a Sub in my MS Access database that opens the word document and updates some DOCVARIABLES in it but I don't know how to address the embedded graph to update its values. Can anyone help me figure out how to do that?

Following are the relevant lines of code that I am using:
Set docNew = WordApp.Documents.Add(Application.CurrentProject.Path & "\Proformas\SurveyReportProForma.doc")
   docNew.Variables.Add Name:="Suburb", Value:=Sz(Me.Parent.Suburb, True)
   docNew.Fields.Update 'update the Word document with the new variable values
   docNew.Fields.Unlink 'display the document with the new values not the links
0
Rob4077
Asked:
Rob4077
  • 13
  • 7
  • 5
  • +1
1 Solution
 
Andrew_WebsterCommented:
Try recording a macro in Word while doing what you need to do, then inspect the macro and use the code as the basis of your code in Access.
0
 
Jeffrey CoachmanMIS LiasonCommented:
If it were me, I would create the chart in Excel and "Link" it into Word.

This way simply double-clicking the graph would open up Excel where the data would be easily edited.

I mean, ...you are editing the data manually now anyway, ...at least have the data in Excel where you have this data saved and other Features are available.

JeffCoachman
0
 
Rob4077Author Commented:
Hi Andrew, I tried your suggestion but when I start recording and try to right click on the chart it will not respond, i.e. the ability to edit the chart is not available in the recording function.

Hi JeffCoachman, I don't really want to have to edit the data manually. I was hoping to find some way to press a button from a form in MS Access and have the VBA code take the data from an Access table and have it update the chart in MS Word.

Perhaps there is an easier solution and you may be able to help me work that one. I already have the full report working in MS Access and doing exactly what I need. The reason I want the output in Word format instead of an MS Access report is that I want the user to have the ability to edit the content of the report, not necessarily the chart. Is there a way that I construct a chart in MS Access and copy it into a Word document, even if that means inserting the chart as an uneditable image of some sort?

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nick67Commented:
I don't have Word involved, but I do have a Chart8 object on an Access report.
Chart Objects have a rowsource property.
That can be any SQL you can craft.
The trick is to set it equal to an Access saved query

And THEN use a querydef to muck with it

Sample attached.
Ask question about the sample if the technique is mystifying.
The form passes in an opening argument.
The report OnOpen the mucks with the querydef underlying the chart

Charts are tough, man
I got skull-cramps :(
0
 
Rob4077Author Commented:
Did you attach a sample?

I have managed to get this working in a report exactly the way I want it. How would I take that object and paste it into a report?
0
 
Nick67Commented:
Sorry,  I am not sure why the sample didn't go.

Try, Try again

In the sample <sheepish grin> I copied and pasted from my working report.
It may play the same for you.
chart.mdb
0
 
Rob4077Author Commented:
PS I meant I got it to work in and MS Access Report, not in MS Word. How would I take that object and paste it into a report?
0
 
Nick67Commented:
Now you're not asking for much :)
Short answer: I don't know.
Long answer: I learn darn near as much as I teach.  Give me a bit and I'll see.

Can you post a working db?
0
 
Nick67Commented:
Ok,

if you right click the Access chart in design view and choose Open, the chart then opens.
You can then go Edit|Copy and copy it.

It can then be pasted.
There is little point in pasting it directly to Word as it creates an xls file to put the data in.

VBA editing of the chart data is the next bit to be teased out.
0
 
Nick67Commented:
WOW,

Maybe there is better way to do this
I dunno.
I took the open chart in the Access report and copied it.
I added a chart to an Excel workbook and then pasted into the Excel Chart
I copied the data from the Access query to the Excel workbook Sheet1
I edited the chart to pull its data from Sheet1
I opened the Excel charted and copied it
I pasted special the chart into Word--option link data and keep source formatting.

I also recorded a macro to see how to muck with the dataset of the chart

    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B3:C11")

The upshot: I have, in Word, a chart whose data is on an Excel worksheet, and I know how to tweak the dataset of the chart.
I already knew how to blow a recordset from Access to Excel so the pieces are in place.

I haven't knocked all VBA together to muck the recordset to Excel, and adjust the chart's aim at the data.
If you get stuck, I'll help with that too, but the guts of your question are answered

<how to address the embedded graph to update its values.>

Build the Chart in Excel, and aim it at one of its worksheet.
Link to it in Word
In Access, blow the desired data into the Excel worksheet
Then open the Word doument, and it should work.

The files are all attached
Chart.doc
chart.xls
chart.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I don't really want to have to edit the data manually. I was hoping to find some way to press a button from a form in MS Access>
Oh,
Sorry I missed that...

I only read the first paragraph of your Q.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Nick67,

I see that you have done a fair amount of work on this Q, so you may have a better understanding of the issue at hand.

Access still has the issue of not allowing Linked Excel sheets to update the Access data.
How would your solution work around this?

JeffCoachman
0
 
Nick67Commented:
The original request was to allow Access VBA to alter the data in a Chart displayed in Word.
One way or another, Charts are Excel monsters.
Explicitly creating the chart in Excel, pointed at Excel data permits Access VBA to update the spreadsheet.
The linked Chart in Word gets these updates auto-magically.

The Author said he had a chart nicely configured in an Access report.
Getting that chart off a report, into Excel, and then into Word intact was the other part of my comments
0
 
Jeffrey CoachmanMIS LiasonCommented:
Ah, OK...
0
 
Jeffrey CoachmanMIS LiasonCommented:
Rob4077

Long shot, but...

Is the Word Doc really needed?

What I mean is, ...if the Word doc is just a "document" (and really does not need any "Word Processing Features") then you can simply create an Access Report that displays the same textual data, and simply include the chart.

This would bypass Excel entirely.

Again, a long-shot perhaps, but worth considering...

JeffCoachman
0
 
Nick67Commented:
Definitely, Word adds a lot of wrinkles.
The Chart is still an Excel monster at heart though.
Under the hood, even when the chart has a query for a rowsource, the chart creates an Excel sheet for the life of it's existence and puts its data on it.

You don't have to deal with Excel, true, but the guts are still Excel.

Some day MS will make Chart require a lot less mind-bending on the part of us Access guys.

I'm not holding my breath though :)
0
 
Rob4077Author Commented:
Thanks for your kind support on solving this.

I have created a report in MS Access that displays every bit of data needed in the exact format needed including the associated text etc. In an ideal situation that is all I need. It's quick and very neat.

The problem is that in some instances there may be a need to make some adjustments to the text and layout of the report, not the data in the table or chart. If I could have put the whole report into a Word document then the user would be able to make those final touch ups easily. Because the chart and associated table would not need editing I was hoping there may have been a way to take the chart and table off a report and save it even as a graphic image into the Word document.

I am concerned that if I set it up using the Access>Excel>Word path that you have deveoped it may solve the easy edit problem but that benefit may be offset by possible problems introduced by the complexity of the task. If something goes wrong in the process it could cause the whole application to hang and introduce problems of its own.

Based on your experience, would you implement the Access>Excel>Word approach or do you think it is perhaps too risky?
0
 
Nick67Commented:
It won't hang the app.
Now that the nuts and bolts are worked out, it's a bit of scutwork off the nose, but fairly straightforward from there.
You create a Word template (.dot) with the nicely formatted chart
You create the Excel workbook-and-chart.

Then it's just creating an Access recordset, an Excel application object and

oSheet.Range("A2").CopyFromRecordset rs 'rs being your new data
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B3:C11") 'whatever range you may need

and opening Word.

It'll be some work to get it right, but it'll be bulletproof afterward
0
 
Nick67Commented:
I have remembered that I truly despise charts :(

Partly becuase I don't completely get them, and partly becuase they are just evil.

What version of office are you working with?
Office 2010 throws odd errors if the Excel and Word documents are older file types.
Like unexpected bugs in copyfromrecordset
Like ALWAYS pasting the chart into a .doc as an image instead of a chart.

Like not having the chart see that you have changed the number of series you are using.
Like the chart not updating on open, but needing to be forced by code.

It is still possible, but unless you have frequent need, you're probably better off exporting to PDF and taking the chart as an image manually.

If you really want this, I'll need a sample db and some data to work with
0
 
Rob4077Author Commented:
Hi Nick67, thank you so very much for your help.

I am using MS Office 2007.

I think based on your last post that, although I could get it working with persistence, I may just print the report to PDF  anbd if it needs to be changed I may be better off getting a PDF editor. Then as time permits I will see if I can get the Word version working reliably.

Thanks again for your kind assistance.
0
 
Nick67Commented:
PDF editiors, even Acrobat, are clunky things
An export to PDF or XPS will let you take an image of your chart and paste it into Word, which is about the only reasonable way to do it, because you can copy-and-paste it directly from the open report, and doing so from design view may not give you the data you want.

It could be done, but there are a lot of moving parts :)  and given that I don't know what chart type you're working with, what kinds of data it has, or what changes you are looking to make, it's difficult.

Thanks for the points, and if you ever do stab at it again, post back here, or start a related question, and I'll have a look.

Nick67
0
 
Rob4077Author Commented:
Thanks very much for your added guidance. The chart I am using is a simple pie chart with one exploded slice. I will see what happens over the coming weeks as users start working with the report and see what to do then.
0
 
Nick67Commented:
Typo
<because you can copy-and-paste it directly from the open report>

I meant CAN'T copy-and-paste.

Thank you for letting me know what kind of chart you are working with.
Knowing that it's possible, but that I didn't do it, will pick at my subconscious for a while.
Pie charts are simple ones--I was trying for a multi-bar xy spread to cover worst case--so this may be easier if I tackle it for the hell of it.

Nick
0
 
Rob4077Author Commented:
Sorry you missed that. If you have a look at the first line in the question it said it was a pie chart.

I don't mean to give you a failure complex. I have no doubt that your solution works. The only reason I didn't pursue it completely and immediately was because of your cautions about Access 2010. I am running it on 2007 but I may end up putting it on machines running 2010 if I can get the app to the point where it helps other users too. Actually I would like it to work on 2007 Free Runtime but I suspect that may be asking too much.
0
 
Nick67Commented:
No biggie.  I just hate unsolved puzzles.  I'm not the kind to give up on a suduku once I start!
0
 
Nick67Commented:
No biggie.  I just hate unsolved puzzles.  I'm not the kind to give up on a suduku once I start!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 13
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now