Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Export tables from word doc to excel

Posted on 2003-10-25
2
Medium Priority
?
1,299 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 375 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

571 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