Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping the formatting Excel to Word
Pls disregard this post. I figured it out. Thanks.
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I lock formatting in Excel while keeping the ability to edit? | Excel Discussion (Misc queries) | |||
Pasting table from Word to Excel keeping rows the same | New Users to Excel | |||
Always keeping source formatting when copying and pasting in Excel | Excel Worksheet Functions | |||
paste from excel into word keeping original formating | Setting up and Configuration of Excel | |||
Extracting Information From Excel To Outlook Keeping Formatting | Excel Programming |