How to read data from MS word table and write it in Excel file using VB
Printable View
How to read data from MS word table and write it in Excel file using VB
Welcome to the Forums! :)
There are some things that would change the approach, like: the Excel Workbook should be created in code or it already exists? I assumed both the word doc and Excel workbook already exist and then I made and example for you: For the example used 2 files "sample.doc" (a word doc with 1 table inside) and "sample.xls" both located in C:\, you can change all this to suit your needs. Also you may need some validations if the doc sometimes could not contain a table or maybe if it contains many tables, in this cases more code will be needed to avoid errors and decide which table should be copied.
In order for this to work you need to add 2 references to your project:
1) Microsoft Word XX Object Library (where XX is your Word Version)
2) Microsoft Excel XX Object Library (Idem)
Sample Usage with a button:Code:Private Sub CopyTableToExcel(pDocPath As String, pWBPath As String)
Dim ObjWord As Word.Application, ObjDoc As Word.Document
Dim objExcel As Excel.Application
Screen.MousePointer = vbHourglass
Set ObjWord = New Word.Application
Set ObjDoc = ObjWord.Documents.Open(pDocPath)
ObjWord.DisplayAlerts = wdAlertsNone
Set objExcel = New Excel.Application
objExcel.Workbooks.Open pWBPath
objExcel.DisplayAlerts = False
ObjDoc.Tables.Item(1).Select
ObjWord.Selection.Copy 'Copy the Table in Word
With objExcel.ActiveWorkbook.ActiveSheet
.Range("A1").Select 'The cell where you want the table to be pasted
.Paste
End With
Clipboard.Clear
objExcel.SaveWorkspace
objExcel.Application.Quit
Set objExcel = Nothing
Set ObjDoc = Nothing
ObjWord.Application.Quit
Set ObjWord = Nothing
Screen.MousePointer = vbDefault
End Sub
And 1 more thing, if you want your code to work even in computers with other office versions, you should consider (when your coding is done) convert it to Late Binding. What Late Binding is: You remove your references previously added, change type declarations to Objects, also word/Excel Library variables should be changed by explicit values, and objects should be instantiated by using CreateObject() VB Function. All this is really easy and can be done just when your coding is already done, this is also the best moment to do it since adding Late Binding will remove VB's Intellisense capabilities when working with your Excel and Word Objects, this happens simply because the references to these 2 libs are removed.Code:Private Sub Command1_Click()
CopyTableToExcel "C:\sample.doc", "C:\sample.xls"
End Sub