Link to home
Start Free TrialLog in
Avatar of victoriaharry

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

Avatar of SiddharthRout
Flag of India image

Avatar of 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
End Sub

Open in new window

Avatar of Cory Vandenberg
Cory Vandenberg
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial