Retain excel cell format when copying to word bookmark

[ajhez]

Hi all

I'm using an adapted version of the base code below.

My issue is that one or more of the cells that I copy using the 'CopyCell' are numerical rather than just plain text. When the value is pasted into the respective bookmark it loses its formatting - e.g. "15%" in the excel cell will be pasted as "0.15" in the word document.

I've tried to tweak by using things like CopyCell.Text but no joy. Any ideas?

Thanks!


Option Explicit'change this to where your files are stored
Const FilePath As String = "C:\ajb files\"
Dim wd As New Word.Application
Dim PersonCell As Range
Sub CreateWordDocuments()
'create copy of Word in memory
Dim doc As Word.Document
wd.Visible = True
Dim PersonRange As Range
'create a reference to all the people
Range("A1").Select
Set PersonRange = Range( _
ActiveCell, _
ActiveCell.End(xlDown))
'for each person in list �
For Each PersonCell In PersonRange
'open a document in Word
Set doc = wd.Documents.Open(FilePath & "Word form.docx")
'go to each bookmark and type in details
CopyCell "FirstName", 1
CopyCell "LastName", 2
CopyCell "Company", 3
'save and close this document
doc.SaveAs2 FilePath & "person " & PersonCell.Value & ".docx"
doc.Close
Next PersonCell
wd.Quit
MsgBox "Created files in " & FilePath & "!"
End Sub
Sub CopyCell(BookMarkName As String, ColumnOffset AsInteger)
'copy each cell to relevant Word bookmark
wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Value
End Sub

[Norie]

Using the Text property of a cell should give you the cell value as it's formatted.

How exactly did you try it?

Did you try it in the CopyCell sub?


Sub CopyCell(BookMarkName As String, ColumnOffset AsInteger)
'copy each cell to relevant Word bookmark
wd.Selection.GoTo What:=wdGoToBookmark, Name:=BookMarkName
wd.Selection.TypeText PersonCell.Offset(0, ColumnOffset).Text
EndSub

[ajhez]

@Norie - I foolishly tried it in the first sub and as I posted this realised my mistake - you are of course correct and this worked perfectly!