excel to word

[peter.thompson]

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

[Tim Williams]

For the chart:

Sheet3.ChartObjects("NCF").Chart.CopyPicture _
Appearance:=xlPrinter, Size:=xlScreen, Format:=xlPicture

Tim

"peter.thompson"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[peter.thompson]

Tim,

Thanks a bunch for that, now works fine!...now my attention is on solving the formatting issue

Cheers

Peter

[Tom Ogilvy]

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"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>