PDA

View Full Version : copy excel to word



tommy1234
02-01-2008, 01:16 PM
Hi
I have an excel worksheet that contain data on several user.
I analyze the data for each user separately and create a different worksheet for each one of them.
I call a procedure that copy the analyzed data for each user in a different word document (the data is arranged in tables but the number of the table can be different between each user).
My problem is that a don't know how to design the word document.
For example : how to put a copy table in the center on the document, color the table headline, color the table cells and etc.
On the botton line i want to know how to design copied table for excel in word.

Thanks


Sub excel_word()

Dim wdApp As Object
Dim wdDoc As Object
Dim lastp As Range
Dim t As Integer, j As Integer, m As Integer
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add
wdApp.Visible = True
j = 1
t = 2
m = 0
'define word page layout
wdDoc.PageSetup.RightMargin = 40
wdDoc.PageSetup.LeftMargin = 40
'user name
Range("f2").Copy
wdApp.Selection.Paste
Do While Range("b" & t) <> ""
If Range("b" & t) = "Total" Then
m = t
With wdDoc
'project code as headline
Range("a" & m).Copy
Set range1 = .Content
range1.InsertParagraphAfter
range1.Collapse Direction:=wdCollapseEnd
range1.Paste
range1.Select
With wdApp.Selection.Font
.Size = 18
.Bold = True
.ColorIndex = 6
End With
Range("G1:L1").Copy
Set range2 = .Content
range2.Collapse Direction:=wdCollapseEnd
range2.Paste
range2.Collapse Direction:=wdCollapseEn
wdApp.Selection.Tables(1).Select
With wdApp.Selection
.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With
' copy data for summary report
Range("G" & m, "L" & m).Copy
Set range3 = .Content
range3.Collapse Direction:=wdCollapseEnd
range3.Paste
range3.Collapse Direction:=wdCollapseEn
range3.InsertParagraphAfter
'COPY DATA INCLUDING HEADLINES
Range("b" & j, "d" & m).Copy
Set range4 = .Content
range4.Collapse Direction:=wdCollapseEnd
range4.Paste
range4.Collapse Direction:=wdCollapseEn
range4.InsertParagraphAfter

End With
j = t + 1
End If
t = t + 1
Loop
Application.CutCopyMode = False
End Sub