• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Converting tables on a word 201 documents to excel 2010

Hi,

I've a word file which has no of tables with data, I need to export these table's data into excel or SQL table, is there any sort cut to do this?

Regards,
Manjula
0
keplan
Asked:
keplan
1 Solution
 
regmigrantCommented:
What do you want to do with the data
Have you tried a straight cut and paste or are you looking for a programmatic way to do it on a regular basis?
0
 
Anthony PerkinsCommented:
>>Zones: SQL Server 2008, Microsoft Excel Spreadsheet Software, Microsoft Word<<
Does this have anything to do with SQL Server?
0
 
keplanAuthor Commented:
The Microsoft word file got many tables with data, so it is a big task to copy and paste to excel, i'm looking for some kind of export facility directly from word file to excel or  table on SQL server database. I can use SSIS from excel file to table, but how could I export tables to excel from word file?


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
andrewssd3Commented:
You haven't posted your Word doc, so I don't know exactly what yur data looks like, but this generic macro will run from Excel, and will import all the tables from a Word doc you specify into the active sheet.
Public Sub GetWordTables()

    Dim wdDoc As Object 'Word.Document
    Dim wksTarget As Excel.Worksheet
    Dim rngOut As Excel.Range
    Dim tbl As Object 'Word.Table
    
    Const WORD_SOURCE As String = "YOUR FILE LOCATION...\tablesource.docx"
    
    Set wdDoc = GetObject(WORD_SOURCE)
    
    Set wksTarget = ActiveWorkbook.Worksheets("Sheet1")    ' or whatever you want
    Set rngOut = wksTarget.Cells(1)
    
    For Each tbl In wdDoc.Tables
        tbl.Range.Copy
        
        rngOut.Select
        rngOut.PasteSpecial xlPasteValues
        
        Set rngOut = rngOut.Offset(Selection.Rows.Count, 0)
    Next tbl
    
    Set wdDoc = Nothing

End Sub

Open in new window


0
 
keplanAuthor Commented:
Fantastic answer andrewssd3!

if you could help me on this as well. the unique identifier of each table is the Headline above the word table and also not in the column heading. your macro does the great job to copy all the table to a excel worksheet, since my programe knowledge is bit less, i would like to ask you how do I modify to copy the heading of each tables on the word file which is not on the column's heading.

Thanks,

Sample table
                                                          Heading1

      2009/2010      2010/2011      % change      2009/2010      2010/2011      % change
      data              data                   data             data                           data                        
                                    
                                    
                                    
                                    

                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    

                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    
                                    



Heading

	2009/2010	2010/2011	% change	2009/2010	2010/2011	% change

Open in new window

0
 
andrewssd3Commented:
What do you want to do with the Heading text? - you could create a new first column which has that heading text repeated for each row of the table.  Also do you want any of the heading rows from the table itself, or shall I just throw those away?
0
 
keplanAuthor Commented:
You are exactly talking about what I need, yes, I need to repeat the heading on each row for the table it belong to. and  when the new table found it should get the new heading belong to that table. So the heading rows can thrown away. I would like to see only a one occurence of the heading rows.

Thanks for your help.
0
 
andrewssd3Commented:
This should do what you are looking for.  It gets the header text from the paragraph immediately preceding the table, and fills the first column with that value.

Hope this helps - Stuart
Public Sub GetWordTables()

    Dim wdDoc As Object         'Word.Document
    Dim wksTarget As Excel.Worksheet
    Dim rngOut As Excel.Range
    Dim tbl As Object           'Word.Table
    Dim blnFirst As Boolean
    Dim rngHeader As Object     'Word.Range
    Dim strHeader As String
    
    Const WORD_SOURCE As String = "YOUR LOCATION\tablesource.docx"
    
    Set wdDoc = GetObject(WORD_SOURCE)
    
    Set wksTarget = ActiveWorkbook.Worksheets("Sheet1")    ' or whatever you want
    wksTarget.Activate
    Set rngOut = wksTarget.Cells(1, 2)
    
    blnFirst = True
    For Each tbl In wdDoc.Tables
        tbl.Range.Copy
        ' select the output range and paste the Word table
        rngOut.Select
        rngOut.PasteSpecial xlPasteValues
        
        Set rngOut = Selection
        ' delete the header row unless this is the first table
        If Not blnFirst Then
            rngOut.Rows(1).EntireRow.Delete
        Else
            blnFirst = False
            ' first time, so move past the first row
            Set rngOut = rngOut.Offset(1, 0).Resize(rngOut.Rows.Count - 1, rngOut.Columns.Count)
        End If
        
        ' now find the table header and populate the first column with it
        Set rngHeader = tbl.Range
        ' move one character back from the start of the table - should take us to the para
        ' marker for the header paragraph, then we just get its text
        rngHeader.Collapse 1            ' wdCollapseStart
        rngHeader.MoveStart 1, -1       ' wdCharacter, -1
        strHeader = rngHeader.Paragraphs(1).Range.Text
        
        ' fill the first column with the header text for this table
        rngOut.Offset(0, -1).Resize(rngOut.Rows.Count, 1).Value = strHeader
        
        ' move rngOut on to the next free cell for output
        Set rngOut = rngOut.Offset(rngOut.Rows.Count, 0).Resize(1, 1)
    Next tbl
    
    wksTarget.Cells(1).Select
    
    wdDoc.Close False
    Set wdDoc = Nothing

End Sub

Open in new window

0
 
keplanAuthor Commented:
excellent solution, much appreciated for your help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now