Solved: Excel VBA to format Word doc

[joelle]

Hello Excellers,

Hope you all had a wonderful Xmas time and did not get sick w/ overeating.
I was sick myself -- not from eating frenzy but cooking frenzy -- I made clear-rice-paper shrimp rolls (lot of work!)

Anyhow, hope I could get some vba tip to compensate for that ...
With the help of different vb gurus, I got the codes below that works beautifully. And here is briefly what it does -- that sounds like I posted it before but it is not a repetition because I'd need to extend it to include one more challenge ... know you love vb challenges

As you can see, the codes help me do the followings:

1. copy range A1:A150 to the clipboard
2. open a new Word doc
3. paste the range to the newly opened Word doc
4. (here comes the beauty) turn the pasted date from excel table to a regular Word doc - AND - still keeps the pre-copied formatting (some words are red / some are bold

Here comes the challenge: up to this point, I'd love some extended codes to format the Word doc as follows:
* set the Word doc to 0.8 inch (all 4 margins; left/right/top/bottom)
* set the Word doc to Arial for font, 9 pts for font size

Bigger challenge yet, how to twist the existing codes (at the earlier part) so that the page breaks I set from the original excel range would turn to page breaks at the very same position after the data has been converted to normal Word paragraphs (taken care of on stage #4 above).

I'm so appreciative if someone can help lead me thru these 2 hurdles, or if I can pass the 1st challenge I'm equally so appreciative.
As always, many thanks in advance.
Nee

[VBA]Option Explicit
Sub Screenshot()
'transfers XL range with format to Word doc

Dim Wdapp As Object, wdDoc 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")
Set wdDoc = Wdapp.documents.Add

With Wdapp.activedocument
.Range(0, .Characters.Count).Delete 'clears document
.Range(0).Paste

With .Parent
.Selection.Tables(1).Select
.Selection.Rows.ConvertToText Separator:=0, NestedTables:=True
.Selection.ParagraphFormat.Alignment = 0
End With

End With
Application.CutCopyMode = False
Wdapp.Visible = True
Exit Sub

Errmsg: MsgBox "You have an error"
Wdapp.Quit
Set Wdapp = Nothing

End Sub[/VBA]

[Dave]

Here's a start to the wish list. It sets the document font but doesn't set the margins? As for page breaks I'm not sure. You also don't need to clear the doc as you are creating a new one. This also resets the XL spreadsheet to normal. Good luck and happy holidays. Dave
[VBA]
Sub Screenshot()
'transfers XL range with format to Word doc

Dim Wdapp As Object, wdDoc As Object, Rng As Range, c As Range
Dim DocRng As Object

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")
Set wdDoc = Wdapp.documents.Add

With Wdapp.ActiveDocument.PageSetup
.RightMargin = 4
.LeftMargin = 4
End With


With Wdapp.ActiveDocument
'.Range(0, .Characters.Count).Delete 'clears document
.Range(0).Paste

With .Parent
.Selection.Tables(1).Select
.Selection.Rows.ConvertToText Separator:=0, NestedTables:=True
.Selection.ParagraphFormat.Alignment = 0
End With

End With
Set DocRng = Wdapp.ActiveDocument.Range
With DocRng.Font
.Name = "Arial"
.Size = 9
End With
'reset XL sheet format
For Each c In Rng
c.BorderAround Weight:=xlThin, ColorIndex:=15
Next c


Application.CutCopyMode = False
Wdapp.Visible = True
Exit Sub

Errmsg: MsgBox "You have an error"
Wdapp.Quit
Set Wdapp = Nothing

End Sub
[/VBA]

[joelle]

Hello Dave,

Thanks for the postback. The margin set and font set are good for now. About the page break, yeah, I figure its pretty sticky.

Happy Holidays!

Nee