asked on

VBA - Create Word table and copy excel cell data

Hi Guys,

I'm pulling some data into excel which is generally 5 columns by about 20 rows of data but can differ. What I need to do is select all the data in excel then create a table in word with the same about of columns and rows and then paste the data into the word table.
The word table also needs to have the grid lines separating the columns and rows.
Just wondering if anyone has dome something similar in the past and can offer some advise on the best way to do it with vba code
Thanks

Gavin

SiddharthRout

Here is an example

http://vbaexpress.com/kb/getarticle.php?kb_id=436

Sid

GrahamSkan

This is a Word macro, and needs a reference set to the Microsoft Excel Object Library.

Sub TableFromExcel()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim tbl As Word.Table
    Dim r As Integer
    Dim c As Integer
    Dim b As WdBorderType
    
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Open("C:\MyFolder\MyWorkbook.xls")
    xlApp.Visible = True
    Set xlWks = xlWbk.Sheets(1)
    
    Set tbl = ActiveDocument.Tables.Add(ActiveDocument.Bookmarks("\EndOfDoc").Range, 20, 5)
    For b = wdBorderVertical To wdBorderTop
        tbl.Borders(b).LineStyle = wdLineStyleSingle
        tbl.Borders(b).LineWidth = wdLineWidth025pt
    Next b
    For r = 1 To 20 'start row to end row
        For c = 1 To 5 'startcolumn to end column
            tbl.Cell(r, c).Range.Text = xlWks.Cells(r, c).Value
        Next c
    Next r
    
    xlWbk.Close
    xlApp.Quit
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Cory Vandenberg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial