Solved

Export tables from word doc to excel

Posted on 2003-10-25
2
1,290 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

896 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

14 Experts available now in Live!

Get 1:1 Help Now