joelle
12-27-2005, 11:24 AM
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
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
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
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