[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to count multiple tables in a Word doc from and Excel document?

Posted on 2012-08-24
3
Medium Priority
?
957 Views
Last Modified: 2012-08-24
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!!
0
Comment
Question by:calbais
  • 2
3 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 38330894
Something like

for each tbl in .tables
   For iRow = 1 To tbl.Rows.Count
                sVal = tbl.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
next
       

Open in new window


Chris
0
 

Author Comment

by:calbais
ID: 38330985
Chris, that works perfectly!!

Thank you very much!!
0
 

Author Closing Comment

by:calbais
ID: 38330989
Very quick accurate solution to my problem!!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question