Solved

Export tables from word doc to excel

Posted on 2003-10-25
2
1,293 Views
Last Modified: 2011-11-06
I have a word document full of tables.
I need to transfer these n number of tables to a excel spreadsheet, creating a sheet for each table.
I need to overwrite the same excel spread sheet each time.
Please help me create this macro.
Thanks
0
Comment
Question by:varsityboyz
  • 2
2 Comments
 
LVL 17

Accepted Solution

by:
Suat Ozgur earned 125 total points
ID: 9621388
Hi varsityboyz,

I don't know if there is another better way to do this but following code creates the same table in Excel sheet and change the name of the sheets with the order of the table in the word document (Table1, Table2 etc...).

Please open your document with tables and goto VBA (Alt+F11), select your document's VB Project in Project Explorer (Ctrl+R) and point to Insert->Module to add a new module then copy & paste the following code into the new module displayed at right pane.

Then just run it by hitting F5 (or return Word and Tools->Macro->Macros: Select ExportTables macro to Run).

You should set the xlFileName variable as the excel workbook path & name that you want to overwrite.

'----Code Start----
Sub ExportTables()
Dim doc As Document
Dim tbl As Table
Dim xls As Object
Dim sht As Object
Dim i As Long
Dim xlFileName as String

    On Error GoTo ErrHandler
       
    xlFileName ="C:\tables.xls"

    Set doc = ActiveDocument
    Set xls = CreateObject("Excel.Application")
    Set wrk = xls.workbooks.Add
    xls.DisplayAlerts = False
    For i = 1 To wrk.worksheets.Count - 1
        wrk.worksheets(1).Delete
    Next i
    i = 1
    For Each tbl In doc.Tables
        Set sht = wrk.worksheets.Add(, wrk.worksheets(wrk.worksheets.Count))
        If i = 1 Then
            wrk.worksheets(1).Delete
        End If
        sht.Name = "Table" & CStr(i)
        tbl.Range.Copy
        sht.Cells(1, 1).PasteSpecial -4163
        sht.Cells(1, 1).Select
        i = i + 1
    Next tbl
    wrk.worksheets(1).Select
    wrk.SaveAs xlFileName

    'Remove the Visible method if you want to quit excel after creating worksheets
    xls.Visible = True

    'Activate the next Quit method by removing single quote at the beginning if you want to quit Excel.
    'xls.Quit

ExitHere:
    If Not xls Is Nothing Then
        xls.DisplayAlerts = True
    End If
    Exit Sub
ErrHandler:
    MsgBox Err.Number & "-" & Err.Description, vbOKOnly + vbExclamation, "Error"
    Resume ExitHere
End Sub
'----Code End---


I hope it helps.
Suat
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 9623727
Glad to see that you accepted my comment, cause I can see lots of open questions of yours.

However, why B grade ? I wondered if my code is not perfect enough for you so I'd like to see how you made it perfect.

Thanks
Suat
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now