P
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
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