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
Subscribe to:
Post Comments (Atom)
Import data from SQL
Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...
-
If you want to add a new menu on mouse right click "Workbook Navigation showing you the list of all open workbooks and worksheets in ea...
-
Macro to Export Range in Json Format Option Explicit Sub export_in_json_format() Dim fs As Object Dim jsonfile Dim rangetoex...
No comments:
Post a Comment