Sep 11 2007, 10:32 AM
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.
Sep 11 2007, 01:56 PM
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
Sep 13 2007, 03:11 AM
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.
Dim iResponse As Integer
Dim tTable As Table
For Each tTable In ActiveDocument.Tables
Exit For 'User chose to leave search.
Selection.Text = Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 4)
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.
' 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
LastRow = ActiveSheet.UsedRange.Rows.Count
intNewRow = LastRow + 1
strNewCell = "A" & intNewRow
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here