Monday, August 22, 2011

Copy Tables from multiple word document saved in a folder to Excel using VBA

If you have multiple word documents saved in a folder and you want to copy the tables from each word document to excel worksheet.

Here is the code-

Sub import_word_table_to_excel()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim fldpath
Dim fld, fil As Object
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim tableWord As Word.Table
Dim sdoc As String

' use to choose the folder having word documents

Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)

Set appWord = New Word.Application
appWord.Visible = True
For Each fil In fld.Files

' browse word documents in a folder

If UCase(Right(fil.Path, 4)) = UCase(".doc") Or UCase(Right(fil.Path, 5)) = UCase(".docx") Then
Set docWord = appWord.Documents.Open(fil.Path)
For Each tableWord In docWord.Tables
' copy word tables
' paste it on sheet 1 of excel file
Sheets(1).Paste Destination:=Sheets(1).Range("A65356").End(xlUp).Offset(1, 0)
End If
Next fil

Set tableWord = Nothing
Set docWord = Nothing
Set appWord = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Excel Macro file-

Sample Word Documents

No comments:

Post a Comment