Solved

Export tables from word doc to excel

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
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…

717 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