Solved

Export tables from word doc to excel

Posted on 2003-10-25
2
1,294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 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