-
extract data from multiple tables from multiple word docs into Excel
Hi,
I am trying to find code that would let me extract data from multiple tables from multiple word docs into Excel.
I currently have the code below but it only allows me to extract data from one table. I would like to extract data from all the tables, so all the data from one word doc would be within one excel row.
I have looked up code and tried to figure it out by myself but to no avail.
please help and thanks in advance!
Option Explicit
Sub test()
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oCell As Word.Cell
Dim sPath As String
Dim sFile As String
Dim r As Long
Dim c As Long
Dim Cnt As Long
Application.ScreenUpdating = False
Set oWord = CreateObject("Word.Application")
sPath = "C:\Documents\H" 'change the path accordingly
If Right(sPath, 1) <> "" Then sPath = sPath & ""
sFile = Dir(sPath & "*.doc")
r = 2 'starting row
c = 1 'starting column
Cnt = 0
Do While Len(sFile) > 0
Cnt = Cnt + 1
Set oDoc = oWord.Documents.Open(sPath & sFile)
For Each oCell In oDoc.Tables(1).Range.Cells
Cells(r, c).Value = Replace(oCell.Range.Text, Chr(13) & Chr(7), "")
c = c + 1
Next oCell
oDoc.Close SaveChanges:=False
r = r + 1
c = 1
sFile = Dir
Loop
Application.ScreenUpdating = True
If Cnt = 0 Then
MsgBox "No Word documents were found...", vbExclamation
End If
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules