Pages

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"
Application.FileDialog(msoFileDialogFolderPicker).Show
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
tableWord.Range.Copy
' paste it on sheet 1 of excel file
Sheets(1).Paste Destination:=Sheets(1).Range("A65356").End(xlUp).Offset(1, 0)
Next
docWord.Close
End If
Next fil


appWord.Quit
Sheets(1).Select
Set tableWord = Nothing
Set docWord = Nothing
Set appWord = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub


Excel Macro file- http://www.filefactory.com/file/cd25f50/n/import_ALL_table_of_multiple_word_Documents_to_excel.xlsm

Sample Word Documents

http://www.filefactory.com/file/cd25f56/n/mutiple_docs.zip

No comments:

Post a Comment