Link to home
Start Free TrialLog in
Avatar of calbais
calbaisFlag for Canada

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?

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

Open in new window


Thanks for any help!!
ASKER CERTIFIED SOLUTION
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of calbais

ASKER

Chris, that works perfectly!!

Thank you very much!!
Avatar of calbais

ASKER

Very quick accurate solution to my problem!!