Excel Graph to Word

[Gemino]

Hello, hopefully someone can help me, I'm fairly new to Foxpro and so far google and this forum has been my best friend up until now.

I have written a program that generates a report using word automation populating multiple tables from a database. I have a template file due to ease of formatting (on the users end) which is written to and then re saved in a different place.

What I want to do is to create a graph from data written to excel (or word, I fidled around with using charts in word but it didn't quite work how I had hoped, it doesn't seem to update the excel data if the word table is updated) from a curser. I can link an excel file to a graph in word, however the problem is when I then save the excel template and word template to a new location it doesn't update the link.

I have read multiple forums on Graph automation but none of them seem to be what I am after. I would like to use the Graphs from Excel if possible. Is there a way I can just copy the graph from excel into word through foxpro? I thought about using macros but I would like to keep away from that if possible - but beggers can't be choosers.


I can post any of my code if needed.

Thanks for your help.

Gemino

 

[Gemino]

I should note, I am using VFP9 and Office 2007

 

[Gemino]

Well I've worked out how to copy the chart now, now just have to try bring it across to word

 

[Gemino]

Well That was much easier than I thought

oExcel.ActiveSheet.ChartObjects("Chart 4").Activate
oExcel.ActiveChart.ChartArea.Copy

oWord.Selection.Paste

The problem I now have is getting it to paste where I want it to in the document, along with certain wrapping formats.

 

[Gemino]

Don't know why I'm replying in here, but its helping my thought process. I just copied it into a text box and all was solved. Probably not the most eloquent way, I'm sure there must be a more efficient way, but it works!

 

[Mike Lewis]

Hi Gemino,

I hope you won't mind me butting into this conversation <g>.

You seem to be on the right track with pasting the Excel chart into a Word document. Regarding your question about how to get the chart into the right position within the document, the usual method is to create a Range object that defines the location in question, and then call that object's Paste method. I won't go into any more details here, but can can do so if you wish.

Welcome to the forum, and to Visual FoxPro. Be sure to come back when you have more questions.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Gemino]

Hi Mike,

you are more than welcome to butt into this... ah.. conversation... haha.

Is there a good website that explains how to create Range Objects? I have dabbled on this recently for adding in a picture into a bookmark, but I couldn't find any references as to what I could define as a range - I would love to use this feature in a few different areas of my report. All I have come across is setting the paragraph or bookmark.

Thanks

Gemino

 

[Mike Lewis]

Gemino,

Working with range objects is pretty fundamental to Word Automation. I'm a bit surprised you've got as far as you have without it.

A good source of information for all aspects of automating Word and Excel from VFP is Microsoft Office Automation with Visual FoxPro, published by Hentzenwerke (see

http://www.hentzenwerke.com/catalog/autofox.htm).

If you haven't yet used ranges, I assume you are using the selection object to identify your current location within the document. If so, you can call that object's Paste method, just as you can with a range:

loWord.Selection.Paste()

One point to keep in mind when pasting a chart: depending on the source of the chart, you might need to use PasteSpecial() rather than Paste(). If you don't, you might end up with just a block of figures (the chart's underlying data), rather than the chart itself. If that happens, try this code:

loWord.Selection.PasteSpecial(.F., .F., 0, .F., 3)

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Gemino]

Hey Mike, thanks for the book link, it is quite good.

I have avoided using ranges (only because I didn't understand how to use them) by having a template form already made up, and then having code that searches for and reads anything between "<>" symbols in the template document, and executes that code - normally a pointer to data from a cursor or array along with executing a program to display as a currency or date.

Tables and Text Boxes have been my best friend for putting in graphs and pictures to avoid formatting issues, but I would like to go back and make things more robust, so the range method seems like the way to go.

The loWord.Selection.Paste() does work, that is what I have been using, but then I have also been saving a copy of the Excel file to the same directory, so probably using PasteSpecial() would be more robust.

Thanks for your time, much appreciated.

You will probably see more questions from me again!

Gemino