Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
Hi,
I am looking to build a macro that will do the following from a single button click in XL 2003: 1) Open a predefined Word document that I am using as a template; 2) Copy a selected chart to a specified cell within a table in the document; 2) Repeat for several charts to several different cells in Word. The Word document is actually an A3 page containing a single 3x3 table. I want to paste (as pictures) 5 different charts to 5 of these cells. The charts are already generated within the source workbook on different sheets. I can copy a given chart to a single cell (chosen by where the cursor is) in an already open document (thanks Jon Peltier), but how do I: 1) Open the Word document from XL (the name and network path are known and will always be the same; 2) Move the cursor between cells in Word so that I can paste the next chart in; 3) Move between XL and Word files within a macro - I can do this between different XL workbooks using Windows(nnn).Activate, is there something similar between applications? If someone can solve these for me, I'm reasonably happy that I can sort the rest of it out. TIA Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
Alternatively....
I can move the cursor between cells using Word macros, so is it possible to call a Word embedded macro from an XL routine? TIA Dave "Risky Dave" wrote: Hi, I am looking to build a macro that will do the following from a single button click in XL 2003: 1) Open a predefined Word document that I am using as a template; 2) Copy a selected chart to a specified cell within a table in the document; 2) Repeat for several charts to several different cells in Word. The Word document is actually an A3 page containing a single 3x3 table. I want to paste (as pictures) 5 different charts to 5 of these cells. The charts are already generated within the source workbook on different sheets. I can copy a given chart to a single cell (chosen by where the cursor is) in an already open document (thanks Jon Peltier), but how do I: 1) Open the Word document from XL (the name and network path are known and will always be the same; 2) Move the cursor between cells in Word so that I can paste the next chart in; 3) Move between XL and Word files within a macro - I can do this between different XL workbooks using Windows(nnn).Activate, is there something similar between applications? If someone can solve these for me, I'm reasonably happy that I can sort the rest of it out. TIA Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
I can move the cursor between cells using Word macros, so is it possible to call a Word embedded macro from an XL routine? It's probably easier to put the code into an Excel macro with the required references, and run it from within Excel. One stop shopping. You can also reference the table in word by column and row. Here is some pseudo-code to enter text into a cell of a Word table: wdApp.Documents("My Report.doc").Tables(1).Cell(2,2).Range.Text = "ABC" If you copy the Excel chart, this pastes it as a pictu wdApp.Documents("My Report.doc").Tables(1).Cell(2,2).Range.PasteAndFor mat wdChartPicture For best results, you should format your chart in Excel exactly as it will appear in Word, so it doesn't even need to be resized. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
Dangerous Dave -
I am looking to build a macro that will do the following from a single button click in XL 2003: 1) Open a predefined Word document that I am using as a template; 2) Copy a selected chart to a specified cell within a table in the document; 2) Repeat for several charts to several different cells in Word. The Word document is actually an A3 page containing a single 3x3 table. I want to paste (as pictures) 5 different charts to 5 of these cells. The charts are already generated within the source workbook on different sheets. I can copy a given chart to a single cell (chosen by where the cursor is) in an already open document (thanks Jon Peltier), but how do I: You're welcome! 1) Open the Word document from XL (the name and network path are known and will always be the same; You need a Word application object (see http://peltiertech.com/Excel/XL_PPT.html) and a document: ' Reference existing instance of Word Set WDApp = GetObject(, "Word.Application") ' Open document Set WDDoc = WDApp.Documents.Open(strPathAndFileName) 2) Move the cursor between cells in Word so that I can paste the next chart in; See your other post. You don't need to move the cursor, just reference the range where the item should be pasted. 3) Move between XL and Word files within a macro - I can do this between different XL workbooks using Windows(nnn).Activate, is there something similar between applications? You don't need to move back and forth, nor to activate objects before working on them, just use code in Excel that references and manipulates the objects in Word. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
Jon,
As ever, my thanks. Glad to see your site is back up :-)) Dave "Jon Peltier" wrote: Dangerous Dave - I am looking to build a macro that will do the following from a single button click in XL 2003: 1) Open a predefined Word document that I am using as a template; 2) Copy a selected chart to a specified cell within a table in the document; 2) Repeat for several charts to several different cells in Word. The Word document is actually an A3 page containing a single 3x3 table. I want to paste (as pictures) 5 different charts to 5 of these cells. The charts are already generated within the source workbook on different sheets. I can copy a given chart to a single cell (chosen by where the cursor is) in an already open document (thanks Jon Peltier), but how do I: You're welcome! 1) Open the Word document from XL (the name and network path are known and will always be the same; You need a Word application object (see http://peltiertech.com/Excel/XL_PPT.html) and a document: ' Reference existing instance of Word Set WDApp = GetObject(, "Word.Application") ' Open document Set WDDoc = WDApp.Documents.Open(strPathAndFileName) 2) Move the cursor between cells in Word so that I can paste the next chart in; See your other post. You don't need to move the cursor, just reference the range where the item should be pasted. 3) Move between XL and Word files within a macro - I can do this between different XL workbooks using Windows(nnn).Activate, is there something similar between applications? You don't need to move back and forth, nor to activate objects before working on them, just use code in Excel that references and manipulates the objects in Word. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
Thanks. The blog is still woefully slow. The hosting company did a major
transition of users to a new platform, without first estimating the required server loads in the new platform. So we're waiting while they install and test out some new server boxes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Risky Dave" wrote in message ... Jon, As ever, my thanks. Glad to see your site is back up :-)) Dave "Jon Peltier" wrote: Dangerous Dave - I am looking to build a macro that will do the following from a single button click in XL 2003: 1) Open a predefined Word document that I am using as a template; 2) Copy a selected chart to a specified cell within a table in the document; 2) Repeat for several charts to several different cells in Word. The Word document is actually an A3 page containing a single 3x3 table. I want to paste (as pictures) 5 different charts to 5 of these cells. The charts are already generated within the source workbook on different sheets. I can copy a given chart to a single cell (chosen by where the cursor is) in an already open document (thanks Jon Peltier), but how do I: You're welcome! 1) Open the Word document from XL (the name and network path are known and will always be the same; You need a Word application object (see http://peltiertech.com/Excel/XL_PPT.html) and a document: ' Reference existing instance of Word Set WDApp = GetObject(, "Word.Application") ' Open document Set WDDoc = WDApp.Documents.Open(strPathAndFileName) 2) Move the cursor between cells in Word so that I can paste the next chart in; See your other post. You don't need to move the cursor, just reference the range where the item should be pasted. 3) Move between XL and Word files within a macro - I can do this between different XL workbooks using Windows(nnn).Activate, is there something similar between applications? You don't need to move back and forth, nor to activate objects before working on them, just use code in Excel that references and manipulates the objects in Word. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Charts to Word
You sound like you know your way around the VBE, which is great. Below is a
macro that I use; it is pretty awesome actually. Sub ControlWordFromXL() Dim objWord As New Word.Application Dim doc As Word.Document Dim bkmk As Word.Bookmark sWdFileName = Application.GetOpenFilename(, , , , False) Set doc = objWord.Documents.Open(sWdFileName) On Error Resume Next Sheets("Sheet1").Activate objWord.ActiveDocument.Variables("First_Name").Val ue = Range("First_Name").Value objWord.ActiveDocument.Variables("Last_Name").Valu e = Range("Last_Name").Value €˜etc€¦€¦. objWord.ActiveDocument.Fields.Update On Error Resume Next objWord.Visible = True End Sub To make this work, you have to insert DocumentVariables in Word. Open Word and click Insert Field Field Name DocVariable (then name the variable€¦in my example above it is called €˜First_Name). Notice: in the macro above it is referenced as: objWord.ActiveDocument.Variables("First_Name").Val ue Notice: you must have a named range in Excel that corresponds to this DocVariable. In Excel I have a range named €˜First_Name, and this is referenced in the macro above as: Range("First_Name").Value Hope That Helps!! Regards, Ryan--- -- RyGuy "Risky Dave" wrote: Jon, As ever, my thanks. Glad to see your site is back up :-)) Dave "Jon Peltier" wrote: Dangerous Dave - I am looking to build a macro that will do the following from a single button click in XL 2003: 1) Open a predefined Word document that I am using as a template; 2) Copy a selected chart to a specified cell within a table in the document; 2) Repeat for several charts to several different cells in Word. The Word document is actually an A3 page containing a single 3x3 table. I want to paste (as pictures) 5 different charts to 5 of these cells. The charts are already generated within the source workbook on different sheets. I can copy a given chart to a single cell (chosen by where the cursor is) in an already open document (thanks Jon Peltier), but how do I: You're welcome! 1) Open the Word document from XL (the name and network path are known and will always be the same; You need a Word application object (see http://peltiertech.com/Excel/XL_PPT.html) and a document: ' Reference existing instance of Word Set WDApp = GetObject(, "Word.Application") ' Open document Set WDDoc = WDApp.Documents.Open(strPathAndFileName) 2) Move the cursor between cells in Word so that I can paste the next chart in; See your other post. You don't need to move the cursor, just reference the range where the item should be pasted. 3) Move between XL and Word files within a macro - I can do this between different XL workbooks using Windows(nnn).Activate, is there something similar between applications? You don't need to move back and forth, nor to activate objects before working on them, just use code in Excel that references and manipulates the objects in Word. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
export Pivot Charts to MS Word? | Excel Discussion (Misc queries) | |||
Copy Charts from Excel to Word | Excel Discussion (Misc queries) | |||
Excel Charts in Word | Charts and Charting in Excel | |||
copying charts into MS Word | Charts and Charting in Excel | |||
Charts to Word | Excel Programming |