PDA

View Full Version : Solved: Excel to Word Number Formatting



rob0923
06-30-2009, 06:18 AM
Hi,

I made a program that copies named cells to word bookmarks that matches the cell name. I got the sources from various examples that I have found. The program seems to be working correctly, but it will not copy currency number format. (Example: $124.60 will come out like 124.6)

I am assuming that it is copying the values as a general text and pasting the value into word. Is there a way with the code I have to change it or will I have to use another method of copying into a word bookmark? I've been searching for the answer, but can't seem to find anything related to this area.

Thanks in advance!


Option Explicit

Sub MergetoTemplate()

Dim appWrd As Object
Dim objDoc As Object
Dim FilePath As String
Dim FileName As String
Dim appExl As Excel.Workbook
Dim ExlNm As Excel.Name

'Assign the word file path and name to
FilePath = ThisWorkbook.Path & "\Template"
FileName = "Temp.doc"

'Set instance of active workbook
Set appExl = ActiveWorkbook

'Open specified Word File
Set appWrd = CreateObject("Word.Application")

'Open specified word file, and Error if File cannot be found
On Error Resume Next
Set objDoc = appWrd.Documents.Add(FilePath & "\" & FileName)

'Error Handling
If objDoc Is Nothing Then
MsgBox "Unable to find the Word file", vbCritical, "File Not Found"
appWrd.Quit
Set appWrd = Nothing
Exit Sub
End If

'Loop through names in the Workbook
For Each ExlNm In appExl.Names

'Place into bookmark if Excel name match Word bookmark
If objDoc.Bookmarks.Exists(ExlNm.Name) Then
objDoc.Bookmarks(ExlNm.Name).Range.Copy = Range(ExlNm.Value)
End If
Next ExlNm

'Display word
appWrd.Visible = True

'Clean up
Set appWrd = Nothing
Set objDoc = Nothing

End Sub

rob0923
06-30-2009, 06:26 AM
Sorry, the copy and paste currently looks like this


'Place into bookmark if Excel name match Word bookmark
If objDoc.Bookmarks.Exists(ExlNm.Name) Then
objDoc.Bookmarks(ExlNm.Name).Range.Text = Range(ExlNm.Value)
End If
Next ExlNm

mdmackillop
06-30-2009, 01:15 PM
Try

objDoc.Bookmarks(ExlNm.Name).Range.Copy = Format(Range(ExlNm.Value) ,"$#,##0.00")

rob0923
06-30-2009, 02:27 PM
Works Perfect! Thanks again!