Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel to word
Would appreciate some help/advice. Am trying to populate a word doc with excel data and charts, using bookmarks 1. I can populate word with data, however it is unformatted. How does one go about turning 123.33 into $123, am assuming format() code needs to go somewhere - would appreciate if someone could show me where the format code should be inserted. 2. Am trying to send a named chart ("NCF") to a word doc using a bookmark, without success so far - the whole excel spreadsheet seems to be copied! Any help on the correct code to copy just the "picture" would be appreciated. Code is below (chart code in red): Cheers Peter Sub Commandbutton6_Click() Dim wdApp As Word.Application Dim wdDoc As Document Dim wdRng As Word.Range Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open("C:\test") wdApp.Visible = True Dim myArray() Dim wdBkmk As String myArray = Array("solution1", "customer1", "author", "date", "solution2", "customer2", "Years1", "tax1", "NCFB", "NPV1", "NPV2", "IRR", "SROI", "PBACK", "WACC", "Hurdle", "Chart1") Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(0)).Range wdRng.InsertBefore (Sheet1.Range("subject")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(1)).Range wdRng.InsertBefore (Sheet1.Range("customer")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(2)).Range wdRng.InsertBefore (Sheet1.Range("author")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(3)).Range wdRng.InsertBefore (Sheet1.Range("date")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(4)).Range wdRng.InsertBefore (Sheet1.Range("subject")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(5)).Range wdRng.InsertBefore (Sheet1.Range("customer")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(6)).Range wdRng.InsertBefore (Sheet1.Range("years")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(7)).Range wdRng.InsertBefore (Sheet1.Range("Tax")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(8)).Range wdRng.InsertBefore (Sheet3.Range("NCFB")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(9)).Range wdRng.InsertBefore (Sheet3.Range("NPV1")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(10)).Range wdRng.InsertBefore (Sheet3.Range("NPV2")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(11)).Range wdRng.InsertBefore (Sheet3.Range("IRR")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(12)).Range wdRng.InsertBefore (Sheet3.Range("SROI")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(13)).Range wdRng.InsertBefore (Sheet6.Range("PBACK")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(14)).Range wdRng.InsertBefore (Sheet1.Range("WACC")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(15)).Range wdRng.InsertBefore (Sheet1.Range("Hurdle_Rate")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(16)).Range Sheet3.ChartObjects("NCF").Copy wdRng.Select wdRng.Application.Selection.Paste Set wdApp = Nothing Set wdRng = Nothing End Sub -- peter.thompson ------------------------------------------------------------------------ peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686 View this thread: http://www.excelforum.com/showthread...hreadid=500051 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel to word
For the chart: Sheet3.ChartObjects("NCF").Chart.CopyPicture _ Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture Tim "peter.thompson" <peter.thompson.21gkyo_1136963416.4131@excelforu m-nospam.com wrote in message news:peter.thompson.21gkyo_1136963416.4131@excelfo rum-nospam.com... Would appreciate some help/advice. Am trying to populate a word doc with excel data and charts, using bookmarks 1. I can populate word with data, however it is unformatted. How does one go about turning 123.33 into $123, am assuming format() code needs to go somewhere - would appreciate if someone could show me where the format code should be inserted. 2. Am trying to send a named chart ("NCF") to a word doc using a bookmark, without success so far - the whole excel spreadsheet seems to be copied! Any help on the correct code to copy just the "picture" would be appreciated. Code is below (chart code in red): Cheers Peter Sub Commandbutton6_Click() Dim wdApp As Word.Application Dim wdDoc As Document Dim wdRng As Word.Range Set wdApp = New Word.Application Set wdDoc = wdApp.Documents.Open("C:\test") wdApp.Visible = True Dim myArray() Dim wdBkmk As String myArray = Array("solution1", "customer1", "author", "date", "solution2", "customer2", "Years1", "tax1", "NCFB", "NPV1", "NPV2", "IRR", "SROI", "PBACK", "WACC", "Hurdle", "Chart1") Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(0)).Range wdRng.InsertBefore (Sheet1.Range("subject")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(1)).Range wdRng.InsertBefore (Sheet1.Range("customer")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(2)).Range wdRng.InsertBefore (Sheet1.Range("author")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(3)).Range wdRng.InsertBefore (Sheet1.Range("date")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(4)).Range wdRng.InsertBefore (Sheet1.Range("subject")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(5)).Range wdRng.InsertBefore (Sheet1.Range("customer")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(6)).Range wdRng.InsertBefore (Sheet1.Range("years")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(7)).Range wdRng.InsertBefore (Sheet1.Range("Tax")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(8)).Range wdRng.InsertBefore (Sheet3.Range("NCFB")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(9)).Range wdRng.InsertBefore (Sheet3.Range("NPV1")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(10)).Range wdRng.InsertBefore (Sheet3.Range("NPV2")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(11)).Range wdRng.InsertBefore (Sheet3.Range("IRR")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(12)).Range wdRng.InsertBefore (Sheet3.Range("SROI")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(13)).Range wdRng.InsertBefore (Sheet6.Range("PBACK")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(14)).Range wdRng.InsertBefore (Sheet1.Range("WACC")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(15)).Range wdRng.InsertBefore (Sheet1.Range("Hurdle_Rate")) Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(16)).Range Sheet3.ChartObjects("NCF").Copy wdRng.Select wdRng.Application.Selection.Paste Set wdApp = Nothing Set wdRng = Nothing End Sub -- peter.thompson ------------------------------------------------------------------------ peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686 View this thread: http://www.excelforum.com/showthread...hreadid=500051 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel to word
Tim, Thanks a bunch for that, now works fine!...now my attention is on solving the formatting issue Cheers Peter -- peter.thompson ------------------------------------------------------------------------ peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686 View this thread: http://www.excelforum.com/showthread...hreadid=500051 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel to word
If it is formatted in the cell that way, then try
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(7)).Range wdRng.InsertBefore (Sheet1.Range("Tax").Text) if not Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(7)).Range wdRng.InsertBefore (Format(Sheet1.Range("Tax"),"$#,##0")) assuming Range Tax contains a number such as you describe. -- Regards, Tom Ogilvy "peter.thompson" <peter.thompson.21gqim_1136970605.2089@excelforu m-nospam.com wrote in message news:peter.thompson.21gqim_1136970605.2089@excelfo rum-nospam.com... Tim, Thanks a bunch for that, now works fine!...now my attention is on solving the formatting issue Cheers Peter -- peter.thompson ------------------------------------------------------------------------ peter.thompson's Profile: http://www.excelforum.com/member.php...o&userid=29686 View this thread: http://www.excelforum.com/showthread...hreadid=500051 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Excel count of 1 word if found in multi-word cells of column | Excel Worksheet Functions | |||
Excel 7, paste linked to word becomes black when word pdf'd | Excel Discussion (Misc queries) | |||
Copy from Word to Excel, and retain indent, plus word wrap | Excel Discussion (Misc queries) | |||
Print labels by using Excel data in a Word mail into word | Excel Discussion (Misc queries) | |||
Printing Word Document using Excel Programming hangs Word | Excel Programming |