Hi all,

I have a question about writing a macro in Excel. I have done a lot of VBA in Access, but not in other office programs. I was wondering if there is a way to take a table from a Word document and put it into an Excel spreadsheet by using a macro.

thanks

Okay, maybe a different approach will prompt some answers. Right now, I have a sample word document embedded in my worksheet. Is there anyway to open it up, copy all, close it and paste the documents into the spreadsheet??

If there is a better way to do it, I am open to ideas

Thanks

Hi, I wanted to do the same thing today. I have never done any Macros but I have large tables in over a thousand word documents that I need to copy into one spreadsheet and the tediousness of this is killing me.
No one seemed to have a good answer so I just hacked on the computer until I finally figured it out.

First I created two macros in Word. The macro finds the first table in the document, then it adds a column with the files name because I happen to need that. (I wasn't able to figure out how to add a single column but I deleted the extras after it was in Excel) It then selects the table and copies it.
Then it closes the document but does not save the changes. If you have multiple tables you can choose to save changes and let it run through again for each table.


Sub CopyWordTable()
Dim iResponse As Integer
Dim tTable As Table
CopyFileNameToBuffer
For Each tTable In ActiveDocument.Tables
tTable.Select
Selection.InsertColumns
Selection.Paste
tTable.Select
Exit For 'User chose to leave search.
Next
Selection.Cut
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Application.Quit
End Sub




Sub CopyFileNameToBuffer()
Selection.Text = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 4)
Selection.Cut
End Sub



Now the Word text is in the buffer and you can paste it in Excel but I went a step further.

I wrote an Excel Macro that controls the whole process. It tells Word to open up the Word file, runs the Word macro and then automatically pastes the contents into the next free row on my Excel worksheet.

I am still working on a for loop that opens each word file in a particular directory. Right now the file name of the word document is specified in the macro.


Sub ControlWord()
' You must pick Microsoft Word 8.0 from Tools>References
' in the VB editor to execute Word commands.

Dim appWD As Word.Application
' Create a new instance of Word & make it visible
Set appWD = CreateObject("Word.Application.8")
appWD.Visible = True

appWD.Documents.Open ("C:\temp\429.doc")
appWD.Application.Run MacroName:="CopyWordTable"


Sheets("Policies").Select
LastRow = ActiveSheet.UsedRange.Rows.Count
intNewRow = LastRow + 1
strNewCell = "A" & intNewRow
ActiveSheet.Range(strNewCell).Activate
ActiveSheet.Paste

End Sub