calbais
asked on
How to count multiple tables in a Word doc from and Excel document?
I have an Excel document that gets information from a Word document consisting of multiple tables.
My problem is that the Excel doc only gets info from the first table and ignores all other tables.
In the code below, you will see "Tables(1)" and that is where my problem lies, I think.
How can I get the code to collect information from Table 1 then go on to Table 2,3,4 etc until it reaches the end of the document?
Thanks for any help!!
My problem is that the Excel doc only gets info from the first table and ignores all other tables.
In the code below, you will see "Tables(1)" and that is where my problem lies, I think.
How can I get the code to collect information from Table 1 then go on to Table 2,3,4 etc until it reaches the end of the document?
Sub LoadTable()
Dim oFSO As Object, oFile As Object, wd As Word.Application, iRow As Integer, lRow As Long
Dim wsSIN As Worksheet
Dim sVal
'==================VERY IMPORTANT!!=============
'set a reference to MS Word Object Library in Tools > Reference. Then check "Microsoft Word 14 Object Library
'===============================================
ScreenUpdating = False
Set wd = CreateObject("word.Application")
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set wsSIN = Sheets("SIN Table")
lRow = wsSIN.Range("A" & Rows.Count).End(xlUp).Row 'gets the row index of the last SIN number in Column A
'lRow = wsSIN.[A1].CurrentRegion.Rows.Count + 1
'******* put the path to your folder in the GetFolder argument *******
For Each oFile In oFSO.GetFolder("C:\ExcelTest").Files
With wd.Documents.Open(oFile.Path)
For iRow = 1 To .Tables(1).Rows.Count
sVal = .Tables(1).Cell(iRow, 3) ' The "3" here specifies the column to get info from
wsSIN.Cells(lRow, "A").Value = Left(sVal, Len(sVal) - 2) ' "A" specifies the column info from column 3 is written to
wsSIN.Cells(lRow, "D").Value = oFile.Name ' "D" specifies the column the filenames are written to
lRow = lRow + 1
Next
.Close
End With
Next
Set wsSIN = Nothing
Set oFSO = Nothing
Set wd = Nothing
ScreenUpdating = True
End Sub
Thanks for any help!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very quick accurate solution to my problem!!
ASKER
Thank you very much!!