extracting text & numbers from excel

[Peter T in Oz]

Am new to VBA. I want to excel to open a word doc and populate it with some
data from excel. I have set up bookmarks in the word doc.

Have got it to work, however the numbers are unformatted. How do I go about
format code to ensure that 12345.123 is presented in word as $12,345??

Any help would be much appreciated.

Cheers

Peter



Am using the following code in an excel worksheet :

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")

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 wdApp = Nothing
Set wdRng = Nothing

End Sub

 

[Doug Robbins - Word MVP]

Use the Format() function


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter T in Oz]

Thanks Doug, where in the code shoud this appear?
Tks

 

[Doug Robbins - Word MVP]

wdRng.InsertBefore Format((Sheet3.Range("NCFB")), "$#,###.00")


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter T in Oz]

Thanks Doug, your help is much appreciated - now I have things working...so
far