Copying Data From WORD to EXCEL

[Opv]

I will be receiving about 24 WORD documents with information that needs to be copied and pasted into a single EXCEL document.

I am looking for a VBA script that will look in a folder called TEMP, open (if opening is necessary or otherwise extract the content from) each WORD document and then paste it into a single new EXCEL document.

Is this something that is doable?

Thanks,

Opv

[fumei]

Yes, it is do-able. However, you need to give much better detail. For example:

"information that needs to be copied and pasted"

Does not tell us anything. "information"???? In a bookmark? From the header? From formfields? From other fields? Easily identifiable paragraphs? From paragraphs that you need to search for?

Have you looked at various examples of similar things?

Yes, you will have to open each file.

Plus, you do not state if you are working from Word - a Word document with VBA that opens each other Word file, gathers the "information", and puts it OUT into an Excel file (newly created???? already existing????).


.....OR......

an Excel file that uses VBA to open the Word files and bring IN the "information".

But yes, it is do-able.

[Opv]

Yes, it is do-able. However, you need to give much better detail. For example:

"information that needs to be copied and pasted"

Does not tell us anything. "information"???? In a bookmark? From the header? From formfields? From other fields? Easily identifiable paragraphs? From paragraphs that you need to search for?
All content from all WORD documents will be copied "as is" and pasted into EXCEL. The content is anywhere from several hundred to several thousand rows of information (not in a table) with a carriage return at the end of each row.


Have you looked at various examples of similar things? I will be happy to if you can point me to one.


Plus, you do not state if you are working from Word - a Word document with VBA that opens each other Word file, gathers the "information", and puts it OUT into an Excel file (newly created???? already existing????).

.....OR......

an Excel file that uses VBA to open the Word files and bring IN the "information".
I will be manipulating the resulting EXCEL document extensively, so I guess if I I'd rather work from Excel and pull the information in.

My inquiry was just a spur of the moment thing. I was thinking if it only required a short snippet of script, it might be worth pursuing. If it is going to be rather involved, it may be more trouble than its worth, as I can always open one WORD document and insert each subsequent document sequentially at the end, then select all, copy and paste into EXCEL.

Thanks,

Opv

[fumei]

OK, so let's assume you are working from Excel. Is this from a new Excel file, or an existing one?

In any case, here are the steps.

1. declare an instance of Word
2. set that instance of Word.
3. use that instance of Word to open the first file
4. use that instance of Word to get the information (you did not answer what form that is in)
5. put that information into the Excel file
6. go on to the next Word file
7. repeat 4 to 6 until you have processed all the Word files
8. destroy the instance of Word.



Done.

It may (or may not) be fairly simple. It depends on how your information is set up in the Word documents...and as you did not bother to answer any questions regarding this...shrug...hard to say.

For example, if the information in all the document was in the same named bookmark, then it would be very easy.

[Opv]

OK, so let's assume you are working from Excel. Is this from a new Excel file, or an existing one?

In any case, here are the steps.

1. declare an instance of Word
2. set that instance of Word.
3. use that instance of Word to open the first file
4. use that instance of Word to get the information (you did not answer what form that is in)
5. put that information into the Excel file
6. go on to the next Word file
7. repeat 4 to 6 until you have processed all the Word files
8. destroy the instance of Word.

Done.

It may (or may not) be fairly simple. It depends on how your information is set up in the Word documents...and as you did not bother to answer any questions regarding this...shrug...hard to say.

For example, if the information in all the document was in the same named bookmark, then it would be very easy.

You lost me at "here are the steps" as this is all over my head. If my previous response is insufficient then it is obviously more complicated than I am able to explain. I am content to just withdraw the question and manually combine the WORD documents and perform a manual copy and paste. To that end, perhaps a forum moderator can just delete the thread.

Thanks,

Opv

[fumei]

"If my previous response is insufficient then it is obviously more complicated than I am able to explain."

No. What would make a difference is if you actually answered the questions. It is not more complicated than you can explain, as you did not really explain anything.

It may be more complicated that you are capable of right now, but perhaps if you tried it would become less complicated. Here is a sample, but because you did not explain anything really, it may not be applicable for you.
Sub GrabIt()
Dim appWord As Word.Application
Dim wrdDoc As Word.Document
Dim file
Dim myPath As String

Dim myArray()
Dim j As Long
Dim TempString As String
Dim var

myPath = "c:\zzz\Test\TestWord\"
file = Dir(myPath & "*.doc")

Set appWord = CreateObject("Word.Application")
Do While file <> ""
Set wrdDoc = appWord.Documents.Open _
(Filename:=myPath & file)
TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)

ReDim Preserve myArray(j)
myArray(j) = TempString
j = j + 1
wrdDoc.Close wdDoNotSaveChanges
Set wrdDoc = Nothing
file = Dir()
Loop

appWord.Quit
Set appWord = Nothing
' now have an array of all the bookmarked data
' from all the documents, so dump into Excel file
Worksheets("Sheet1").Activate
Range("A2").Select
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
For var = 1 To j - 1
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
Next
End Sub
Assumptions:

1. a bunch of Word files (.doc) in the folder "c:\zzz\Test\TestWord"
2. ALL of the files have the desired data in a bookmark named "GetData" Perhaps you can see why I asked the question of where the information is? If it is somewhere else...then you have to get it from that somewhere else.


There. Those are the assumptions. Here are the steps again (slightly modified).


1. declare an instance of Word
Dim appWord As Word.Application

2. set that instance of Word.
Set appWord = CreateObject("Word.Application")

3. use that instance of Word to open the first file
Set wrdDoc = appWord.Documents.Open _
(Filename:=myPath & file)

4. use that instance of Word to get the information (you did not answer what form that is in). I am using it as if in a bookmark.
TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)
' above line not needed if bookamrk does NOT include the paragraph mark


5. add information to an array of all the information
ReDim Preserve myArray(j)
myArray(j) = TempString

6. go on to the next Word file
wrdDoc.Close wdDoNotSaveChanges
Set wrdDoc = Nothing
file = Dir()

7. destroy the instance of Word as you are finished with it
appWord.Quit
Set appWord = Nothing

8. put that information into the Excel file
Worksheets("Sheet1").Activate
Range("A2").Select
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
For var = 1 To j - 1
ActiveCell.Value = myArray(var)
ActiveCell.Offset(1, 0).Activate
Next


Done. The information in the named bookmark in ALL the Word files in the folder c:\zzz\Test\TestWord are placed in an Excel file, starting from A2, and proceeding down.

NOTE!!!! The Dir function actions the files in order, and the default order in a folder is alphabetical.

So the files are (in this example):
four.doc
one.doc
three.doc
two.doc

THUS: the listing in the Excel file is the contents of the Word documents in that order.

A2 = "whatever FOUR.doc data" (the text in the bookmark GetData)
A3 = "1234 from ONE.doc data" (the text in the bookmark GetData)
A4 = "blah blah THREE.doc data" (the text in the bookmark GetData)
A5 = "yadda TWO.doc data) (the text in the bookmark GetData)

It may look complicated, but actually it is not. We can gladly help with the actual code (IMO, this is the least complicated part), but YOU have to do the thinking (IMO, for most people this is the hardest part).

[fumei]

PS.

For those who may critically comment on my Excel code, mea culpa. I know I suck big time with Excel. I use it rarely. Undoubtedly there is a better way to do the actions. I would be happy to find out what those are.

[Mistral80]

Hi All,
I would need help to create a VBA code in EXCEL to do the following:

I have huge amount of Word doc and I need to get information from a table from each doc and copy to Excel. Similar like Opv's needed.
But I don't know how to tell to find the information. These data's are in a table in word. Example:

Req. # Changed?(Yes/No/New)
000 Yes

I only need "000" to copy into Excel. This table looks the same in all word document, but data' are different. So the "000" cell is under "Req #" cell.

In your sample before you you looks for bookmarks:
Can you please help me how to re-write below lines?

TempString = wrdDoc.Bookmarks("GetData").Range.Text
TempString = Left(TempString, Len(TempString) - 1)