export word tables to excel

[Eckard Buchner]

this question must have been asked here before...

I'd like to write a makro that exports word 97 tables into excel 97. Copying
and pasting them is not a solution for me, because I would have to export
the tables regularly. I have not much experience in macro writing, could
someone give me a hint...

Thanks
Eckard

 

[macropod]

Hello Eckard,

A macro would probably do no more than the copy & paste you're doing now. If
you need the data in Excel, it would probably be better to start off with it
in Excel in the first place - it's fairly easy to link part or all of an
Excel spreadsheet to a Word document.

Cheers

 

[Eckard Buchner]

That is a good idea. But I have word documents already that contain word
tables. I really would like to have a macro for that. How can I "load" a
word document in an excel macro? The word object model should contain the
table objects
Thanks
Eckard

 

[macropod]

Hi Eckard,

The following code (for running from Excel) should get you going. It uses
early binding, so requires a reference to be set up in advance to the Word #
Object Library (In the VBE, Tools|References, then scroll down till you find
it).

Sub openWordDoc()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(Filename:="D:\Work\Contacts.doc")
wdApp.Visible = True
'Call or insert your table copying code here
wdApp.ActiveDocument.Save
wdApp.ActiveDocument.Close
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

Cheers

 

[Eckard Buchner]

Great, thank you for your help! I've got a macro now, and it is working
fine.

Is there a way to keep Word from asking "save changes?" before it quits?
Something I'm doing in my macro (see below) seems to change the document

Thanks
Eckard

' ------------------------------
Sub ImportDoc()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdTable As Word.Table
Dim wdRow As Word.Row
Dim wdCell As Word.Cell
Dim nRow, nCol, nTab
Dim bHeader As Boolean

Set wdApp = New Word.Application
Set wdDoc =
wdApp.Documents.Open(FileName:="K:\Projekte\SironWeb\TDI-Integration\SironWe
b Integration TDI.doc")
wdApp.Visible = True
'Call or insert your table copying code here

Range("A1:E999").Clear

nTab = 1
nRow = 1

For Each wdTable In wdDoc.tables

nTab = nTab + 1
bHeader = True

For Each wdRow In wdTable.rows
nCol = 1
nRow = nRow + 1
For Each wdCell In wdRow.cells
nCol = nCol + 1

With ActiveSheet.cells(nRow, nCol)
.Select
.Font.Bold = bHeader
If bHeader Then
.HorizontalAlignment = xlCenter
Else
.HorizontalAlignment = xlHAlignGeneral
End If
End With

wdCell.Range.Copy
ActiveSheet.Paste

Next
bHeader = False
Next
nRow = nRow + 1
Next


'wdApp.ActiveDocument.Save
'wdApp.ActiveDocument.Close
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

 

[macropod]

Hi Eckard,

You could save the document before closing, as per the code you've commented
out, or you could change that line to:
wdApp.ActiveDocument.Saved = True
Alternatively, you could use:
wdApp.ActiveDocument.Application.DisplayAlerts = False
at the same point
Cheers

 

[Ruby Tuesday]

Will this script work for Word/Excel XP or Word/Excel 2003?

Thanks