Excel VBA to Word bookmark with Excel cell format retained

Question

I have a VBA code snippet that extracts Excel cell data to a Word template, using bookmarks in the template to assign values to specific template locations. I am able to export Excel cell values in text format to the bookmarks, but cannot retain the native Excel formatting.

The code uses the .Value property to extract data in text formatting. I'm trying to minimize manual formatting a second time in Word (converting a text exported value of 150001.22 from the DENT_90 Excel range to $150,001.22 for the DENT_90 bookmark in Word, for example).

Any idea on how this might be done, such that I can export to Word using the Excel currency cell formatting? I've looked at some explanations for FormatText, but can't seem to get it to work with the following snippet:

Sub TestMemoGen()
Dim objWord As Object
Dim ws2 As Worksheet

Application.ScreenUpdating = False
Set ws2 = ThisWorkbook.Sheets("Word Export")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "C:\Users\ Tester\Word Test Sim.docm"

With objWord.ActiveDocument
.Bookmarks("DENT_90").Range.Text = ws2.Range("DENT_90").Value
End With
Set objWord = Nothing
Application.EnableEvents = True
End Sub

Thanks for any and all suggestions. I have about a hundred bookmarks linked to different templates, so this will be a huge help if formatting can be exported natively from Excel.

Thanks Tim and @ScottHoltzman - thanks for the tips, as the .Text format worked like a charm. I'm going to also look at Scott's suggestion of the Format example to incorporate this code into other parts of my code. — JoeBeef

Matthew Miner Matthew Miner · Accepted Answer · 2016-07-25T22:38:16

.Value is only the "text" of the cell, in this case the number. It doesn't carry formatting info with it. You need NumberFormat.

<Some number>  = ws2.Range("DENT_90").Value
<the format>   = ws2.Range("DENT_90").DisplayFormat.NumberFormat

A little test shows:

Sub junk()
    MsgBox "format=" & Application.Range("g2").DisplayFormat.NumberFormat    
End Sub

So, info from Excel cell is "format=#,##0".

In the Word doc, you can then use format() as described above by Scott, or at: https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

As a side note, did you know you can try things and record the functions in a macro as you go? Then, look at the recorded macro to see how to do things, like perhaps formatting text as entered, which gives you a little more control over precise formatting...

Excel VBA to Word bookmark with Excel cell format retained

1 Answers