-
Solved: Excel to Word Number Formatting
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!
[vba]
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
[/vba]
-
Sorry, the copy and paste currently looks like this
[VBA]
'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
[/VBA]
-
Try
[vba]
objDoc.Bookmarks(ExlNm.Name).Range.Copy = Format(Range(ExlNm.Value) ,"$#,##0.00")
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Works Perfect! Thanks again!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules