Export Excel Range to Word Document in a Tabular Format
Paste the following piece of code in a general module and pass the range as a parameter. The range will be exported to Word in a tabular format.
Sub Test() Call CopyToWord(Sheet1.Range("A1:D10")) End Sub Sub CopyToWord(rngCopy As Range) Dim appWD As Object 'Word.Application Dim arr() Dim lngRow As Long Dim lngCol As Long arr() = rngCopy Set appWD = CreateObject("Word.Application.8") Application.ScreenUpdating = False appWD.Documents.Add For lngRow = 1 To UBound(arr(), 1) For lngCol = 1 To UBound(arr(), 2) If lngCol = UBound(arr(), 2) Then appWD.Selection.typetext Text:=CStr(arr(lngRow, lngCol)) Else appWD.Selection.typetext Text:=CStr(arr(lngRow, lngCol)) & vbTab End If Next lngCol If lngRow <> UBound(arr(), 1) Then appWD.Selection.TypeParagraph End If Next lngRow appWD.Selection.WholeStory appWD.Selection.ConvertToTable Separator:=1, NumColumns:=UBound(arr(), 2), _ NumRows:=UBound(arr(), 1), AutoFitBehavior:=0 With appWD.Selection.Tables(1) .Style = "Table Grid" .ApplyStyleHeadingRows = True .ApplyStyleLastRow = True .ApplyStyleFirstColumn = True .ApplyStyleLastColumn = True End With appWD.Selection.EndKey Unit:=6 appWD.Visible = True Set appWD = Nothing Application.ScreenUpdating = True End Sub
»
- Vishesh's blog
- Login or register to post comments
- 6946 reads
Recent comments
5 years 36 weeks ago
6 years 22 weeks ago
6 years 34 weeks ago
6 years 37 weeks ago
6 years 38 weeks ago
6 years 43 weeks ago
6 years 52 weeks ago
7 years 2 days ago
7 years 3 days ago
7 years 3 days ago