VBA code in Excel opens Word template and fills bookmarks, but can't get percent formatting right!

[Gordo24]

Excel is my source document which contains my data. Word is my template with my bookmarks. I have created a macro in Excel which opens my template and populates my Word bookmarks. The problem I am having is my data in Excel is a percentage (out to 2 decimals) and when the bookmarks in Word are filled, they appear as .6556 or .55. I need them to appear as 65.56% or 55.00%. Here is my code:

Sub createWordReport()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim CatD As Excel.Range
Dim CatB As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="C:\Documents\Test.doc")
Set CatD = Sheets("Sheet1").Range("A7")
Set CatB = Sheets("Sheet1").Range("A6")

With myDoc.Bookmarks
.Item("CatD").Range.InsertAfter CatD
.Item("CatB").Range.InsertAfter CatB
End With

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub

I was able to create a workaround that works in the .6556 scenario above by doing the following code below and simply adding a % sign after where the bookmark is populated.

Item("CatD").Range.InsertAfter CatD * 100

The problem is this will not work in the 55.00% example as it populates my bookmark as 55%

Basically I need Excel to populate the bookmarks in Word as a percentage out to two decimals. Any help would be gretaly appreciated!

 

[Derek Brown]

Hello and welcome to The Board.
I did it slightly differently - replacing bookmarks instead of inserting after. I could then format the inserted text as I needed it. I used the last part of the bookmark name to identify how it should be formatted.
Have a look at my contribution to the following post:
http://www.mrexcel.com/forum/showthread.php?t=491240

 

[Norie]

If the cells in Excel are formatted as percentages you can try using their Text property.

With myDoc.Bookmarks
.Item("CatD").Range.InsertAfter CatD.Text
.Item("CatB").Range.InsertAfter CatB.Text
End With

 

[Gordo24]

Norie....Excellent! Worked perfectly. Thanks for the help!!