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 )

 

[Tal71]

I've played around with inserting the chart in word as you suggested and then using the edit feature to update the data however it's not an effective solution as I can see it right now. Firstly, the data it takes you to to edit isn't the data that needs to be updated by the user (which is located on a different location of the spreadsheet) and secondly, I also need to display this data that is changed by the user in a table on the document, as well as just the chart so I don't think this option is going to work for me. I did think about putting a macro button in to the spreadsheet in the location the edit feature takes you to (to ask the user for the required data) but discovered the macro won't run from within word - for safety reasons I assume.

 

[Tal71]

In addition to my last reply (which is pending moderation) I just realised this isn't likely to work for me anyway given the fact that the master document will be a RTF file so I doubt the embedded graph/worksheet option will be useable?

 

[Macropod]

I just realised this isn't likely to work for me anyway given the fact that the master document will be a RTF file so I doubt the embedded graph/worksheet option will be useable?

As I said previously, the basic chart could be stored in whatever document you're already running your macros from. The same goes for the related table. All you macro then need do is copy each into the RTF file, where they can be updated.

 

[Macropod]

I've played around with inserting the chart in word as you suggested and then using the edit feature to update the data however it's not an effective solution as I can see it right now. Firstly, the data it takes you to to edit isn't the data that needs to be updated by the user (which is located on a different location of the spreadsheet)

So relocate the existing chart over the range concerned before copying & pasting into Word...