PDA

View Full Version : Solved: Different quest on Excel -> Word



joelle
10-31-2005, 04:26 PM
Dear Experts,

Is there a VBA that can turn a form letter in Excel to Word and get it to display in Word format, meaning, the blank rows that separate the paragraph in Excel will turn to blank paragraph lines in Word, and, the "bold" words in a paragraph will remain bold after the letter is transferred to a Word doc.

Do I need to have MS 2003 to achieve this? Pls see picture attached.
(I hope there's a way to attach my ws to this post)

Thanks a lot in advance!

Nee

Dave
11-01-2005, 06:25 AM
Not sure about the expert part but this code seems to work. Good luck. dave

Option Explicit

Sub Screenshot()
'transfers XL range with format to Word doc
'makes changes to "c:\Test.doc" Change directory to suit
Dim Wdapp As Object
Sheets("sheet1").Range("A1:A37").Select 'change range to suit
Selection.CopyPicture xlScreen, Format:=xlBitmap
On Error GoTo Errmsg
Set Wdapp = CreateObject("Word.Application")
Wdapp.ChangeFileOpenDirectory "c:\"
Wdapp.documents.Open Filename:="Test.doc"
With Wdapp.activedocument
.Range(0, .Characters.Count).Delete 'clears document
.Range(0).Paste
End With
Application.CutCopyMode = False
Wdapp.activedocument.Close savechanges:=True
Wdapp.Quit
Set Wdapp = Nothing
Exit Sub
Errmsg: MsgBox "You have an error"
Wdapp.Quit
Set Wdapp = Nothing
End Sub

Dave
11-01-2005, 09:26 AM
This is better. Dave

Option Explicit
Sub Screenshot()
'transfers XL range with format to Word doc
'makes changes to "c:\Test.doc" Change directory to suit
Dim Wdapp As Object, Rng As Range, c As Range
Sheets("sheet1").Range("A1:A37").Select 'change range to suit
Set Rng = Selection
For Each c In Rng
c.BorderAround Weight:=xlThin, ColorIndex:=2
Next c
Selection.Copy 'Picture xlScreen, Format:=xlBitmap
On Error GoTo Errmsg
Set Wdapp = CreateObject("Word.Application")
Wdapp.ChangeFileOpenDirectory "c:\"
Wdapp.documents.Open Filename:="Test.doc"
With Wdapp.activedocument
.Range(0, .Characters.Count).Delete 'clears document
.Range(0).Paste
End With
Application.CutCopyMode = False
Wdapp.activedocument.Close savechanges:=True
Wdapp.Quit
Set Wdapp = Nothing
Exit Sub
Errmsg: MsgBox "You have an error"
Wdapp.Quit
Set Wdapp = Nothing
End Sub

joelle
11-01-2005, 09:57 AM
Hello Dave,

Thank you for your generosity in giving away your codes. I love that the fact that they can retrieve the Word doc from the C: drive http://vbaexpress.com/forum/images/smilies/023.gif

I cant wait to see the codes paste the Excel letter over and turn it to Word format. I probably did some wrong -- and all I got was an error msg http://vbaexpress.com/forum/images/smilies/sad2.gif
Plus, is there a way to simplify the codes so that:
* it leaves the Word doc "open" after it pastes the Excel letter over,
* and it does not display the error msg? ... since some people in my company are very shy of seeing pop-up msg ...

Your huge help is more than appreciated.

Nee

Dave
11-01-2005, 12:05 PM
Nee you have to have a Word document called "Test" on your "C" drive in order for this code to work. Also, the "Test" document must be closed when the sub is run. These are 2 common errors with this approach and that is why the error code is needed. It is possible to include a function to avoid these errors. Just replace Wdapp.activedocument.Close savechanges:=True with Wdapp.Visible = True to see the document. Dave

joelle
11-01-2005, 12:20 PM
Dear Dave,

Thanks for the additional guides -- the vba now works beautifully.
This was a challenge for me that lingered for weeks ...
so, many many THANKS!!

Best regards,

Nee

Dave
11-01-2005, 12:33 PM
You are welcome. I learned a bit myself. Have a nice day. Dave