Keeping the formatting Excel to Word
 

I'm totally new to Excel programming and I found this code and used it.
It works except now I need to figure out how to change the formats
(currency, dates, etc).

I'm trying to complete a word document with the data from Excel.

here's the code I copied and used:
------------------------
Sub CreateWordDoc()

Dim wdApp As Object
Dim wdDoc As Object
Dim rSSN As Range
Dim rPerpName As Range
Dim rRestitution As Range
Dim rBalOP As Range

Set rSSN = Sheet1.Range("B15:B15")
Set rPerpName = Sheet1.Range("B17:B17")
Set rRestitution = Sheet1.Range("B31:B31")
Set rBalOP = Sheet1.Range("B32:B32")

'open the word documents
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\sample.dot")

'replace the bookmarks with the variables
FillBookmark wdDoc, rSSN, "mSSN"
FillBookmark wdDoc, rPerpName, "mPerpName"
FillBookmark wdDoc, rRestitution, "mRestitution"
FillBookmark wdDoc, rBalOP, "mBalOP"


'show the word document
wdApp.Visible = True

End Sub
-------------------------------

I configured the {Formtext} fields in my 'multipleOP.dot' to their
specific formats (ie. currency, SSN). However, each time I run the
macro from Excel, it pastes the correct figures except it didn't use
any formatting (99999.99 vs. $99,999.99) I would like to make sure the
amounts have the $ and separators when populated in the word document.
I've searched the other posts but could not locate anything that might
help me. Please help.

Another question. Is there a way this macro can be written to open a
new blank template? I would like for the users to be able to select
"SAVE AS" only when saving the word document. (similar to opening a
template and instead of opening Sample.dot -- it will open Document1).


Thank you,
Sharon

Keeping the formatting Excel to Word
 

Pls disregard this post. I figured it out. Thanks.

Keeping the formatting Excel to Word
 

Sharon,

Try using the .text property of the range. The default is the .value
property which does not contain the format.

FillBookmark wdDoc, rSSN.Text, "mSSN"

Robin Hammond
www.enhanceddatasystems.com


wrote in message
oups.com...
I'm totally new to Excel programming and I found this code and used it.
It works except now I need to figure out how to change the formats
(currency, dates, etc).

I'm trying to complete a word document with the data from Excel.

here's the code I copied and used:
------------------------
Sub CreateWordDoc()

Dim wdApp As Object
Dim wdDoc As Object
Dim rSSN As Range
Dim rPerpName As Range
Dim rRestitution As Range
Dim rBalOP As Range

Set rSSN = Sheet1.Range("B15:B15")
Set rPerpName = Sheet1.Range("B17:B17")
Set rRestitution = Sheet1.Range("B31:B31")
Set rBalOP = Sheet1.Range("B32:B32")

'open the word documents
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\sample.dot")

'replace the bookmarks with the variables
FillBookmark wdDoc, rSSN, "mSSN"
FillBookmark wdDoc, rPerpName, "mPerpName"
FillBookmark wdDoc, rRestitution, "mRestitution"
FillBookmark wdDoc, rBalOP, "mBalOP"


'show the word document
wdApp.Visible = True

End Sub
-------------------------------

I configured the {Formtext} fields in my 'multipleOP.dot' to their
specific formats (ie. currency, SSN). However, each time I run the
macro from Excel, it pastes the correct figures except it didn't use
any formatting (99999.99 vs. $99,999.99) I would like to make sure the
amounts have the $ and separators when populated in the word document.
I've searched the other posts but could not locate anything that might
help me. Please help.

Another question. Is there a way this macro can be written to open a
new blank template? I would like for the users to be able to select
"SAVE AS" only when saving the word document. (similar to opening a
template and instead of opening Sample.dot -- it will open Document1).


Thank you,
Sharon