Copy data & chart from Excel to Word

[Tal71]

I have a Word document that I already run several macros on. I now want to add an additional macro that will do the following (I'm not sure if this can all be done through a Word macro or if after opening the spreadsheet I need to run another macro through Excel.)

• From the open Word document (named "workingquote.docx",) open a spreadsheet with the name "WorkingSheet.xlsm"
• Prompt user for data to input in several cells (I figured out the code to do this part in an excel macro but not sure if it can be done through the word macro?) This data updates a graph
• Copy a range of cells in excel document to the clipboard
• Switch back to the word document named "workingquote.docx" & go to a bookmark named "ROITable" and paste the data from the clipboard
• Switch back to Excel doc "WorkingSheet.xlsm", copy a graph named "SolarValue"
• Switch back to the word document named "workingquote.docx" & go to a bookmark named "ROIGraph" & paste into the Word document named "workingquote.docx".
• Close the excel document (don't need to save it but do need to retain the data in the word document)

(Cross posted to https://www.excelforum.com/excel-pr...9-copy-data-and-chart-from-excel-to-word.html & http://www.msofficeforums.com/word-vba/40080-open-excel-copy-paste-data-chart.html )

 

[Macropod]

There are many examples of code on all three forums for automating Word from Excel and vice-versa. I suggest you do a search for some code you can adapt and, if you run into difficulties with the implementation, post back with the specifics.

 

[Tal71]

I've been searching for days trying to find a solution but nothing I've tried so far has worked which is why I decided to post the scenario specifically to see if I could get some help. I'm not great with code but I can usually figure out enough from other people's examples to achieve what I want, just not this time around sadly

 

[Macropod]

You say:

I have a Word document that I already run several macros on.

but then you say:

the open Word document (named "workingquote.docx",) open a spreadsheet

A docx document cannot contain macros. So where is all this automation occurring?

Moreover, if all this copying & pasting refers to the same set of cells & graphs, why not simply create a Word template, into which you paste these ranges from Excel as links? That way, any time you create a new document from the template, the links will update automatically without the need for any macros; all you'll now need a macro for is to break the links in the new document so they no longer updates from later changes to the workbook - and even that could be done without a macro.

 

[Tal71]

The marcos aren't specific to the one document as we're using it as a template. The primary document is an rtf that is loaded to an external (browser based) program, that program then inserts data (client & invoice details etc) to the rft template, saves it as a new document, we then download that document, run some macros on it to add additional information and save it as a new document to forward to the client. As the documents are changing several times and start out as an RFT file, the macros can't be saved to the document itself. I'm not sure I explained that very well but hope you understand enough to figure out where I'm coming from.

I'll have to experiment with pasting ranges as links, it's not something I've played with before, but given that new data needs to be input (specific to each client) each time we run the macro I'm not sure this will really work?

 

[Macropod]

There's no reason links shouldn't work; they could be stored in your template, for example, from where they could be written to the appropriate locations in your document. If you update the Excel data first, the replicated link will reflect that.

 

[Tal71]

That's the problem though, I don't want the end user to have to update excel before opening the document, I want them to only have to actively deal with one document (which is why I was going with input boxes for the excel data run via the macro as I suggested in my first post) - trying to minimise user error for admin staff who aren't tech savvy

 

[Macropod]

So why don't you insert the chart etc. into the template from where can be copied to the document and its data edited in the document, and not involve Excel at all?

 

[Tal71]

you mean create the data and chart in word? I don't know that that's possible for complex calculations and charts is it? I haven't been using office much in the past few years so a lot of my knowledge is pretty out of date but it seems that the data I'm calculating is probably too complicated for Word to process and I didn't know Word could create charts but maybe I'm wrong?

 

[Macropod]

If you copy a chart from Excel to Word, it comes complete with a worksheet containing the underlying data. Double-clicking the chart opens up Excel in Word, presenting the underlying data so you can edit it. Do that and click on the document and the chart will be updated. Simple. Not a macro in sight.