Jump to content
One on One Extensive Advanced Excel Training ×
Business Intelligence & Analytics for Digital Transformation

VBA code to copy table data from word to Excel

Saurabh Jain

Recommended Posts




Sub word_excel_multitable()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

rn = 0
cn = 0
For Each t In ActiveDocument.Tables
For Each r In t.Rows
For Each c In r.Cells

xlSheet.Range("A1").Offset(rn, cn).Value = Left(c, Len(c) - 1)
cn = cn + 1

Next c
cn = 0
rn = rn + 1

Next r

rn = rn + 2
Next t

End Sub
  • The code has to be pasted in module of  word document 
  • Set reference for Excel object library in word so that word can write in Excel 
  • Click Tool > References 




Link to comment
Share on other sites

  • Create New...