Pages

Sunday, October 23, 2011

Copy a range from excel and paste as table in word document

If you want copy a range from excel and paste as table in a new word document. Snapshot below-

Excel Sheet-


Word Document-



Here is the code-

Sub export_range_as_table()
' add a reference to the Word-library
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Dim wrdTable As Word.Table
Dim datacell
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
Set wrdRange = wrdDoc.Range
wrdApp.Visible = True
' create a table
Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=10, NumColumns:=5)

For i = 1 To 10
For j = 1 To 5
datacell = Worksheets(1).Cells(i, j).Value

With wrdTable
'''' adding data to table
With .Cell(i, j).Range
.InsertAfter datacell
' aling the text center
.ParagraphFormat.Alignment = wdAlignParagraphCenter
' apply borders to the table
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
End With

With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
End With
End With
End With
Next j
Next i
' applying background color to first row of table
For j = 1 To 5
With wrdTable
With .Cell(1, j).Range
.Font.Bold = True
.Shading.BackgroundPatternColor = wdColorBlue
End With


End With

Next j
Set wrdDoc = Nothing
Set wrdApp = Nothing
End Sub


Download Excel Macro File

No comments:

Post a Comment