Copy data from Word into Excel

[chinchee]

Hi all,



I need to copy some information from specific cell of a table in Microsoft Words, then insert the data into Microsoft Excel (I have included some example files to show how the data looks like and how I want them to be copied - In actual case, there are more datas in the Words document and I only need to copy from three specific cells).

I found that there is an easy way to do it where you save the .doc as plain text, then open in Excel. My problem is, I have a few thousand documents and it is quite a task to open one file at a time to perform the action. I am wondering if there is any way around it?

Thank you in advance.

[macropod]

Are the cells you want to copy always in the same place in the table? Is there more than one table in the document? If so, how is your table to be identified?

[chinchee]

Hi macropod,

Thank you for your reply. Luckily, the position of the information that I need in the cell is always the same.

I think there is a way to open Words using macro in Excel. However, (i)once in Words, I am not sure how do I ask Words to copy the data and pass back to Excel. (ii)Also, I am not sure how to automate that so that it does that to all the files in a specific folders (I have gathered all the required document in the same folder).

Thank you.

[macropod]

Automating Word from Excel isn't at all difficult. Neither is extracting data that are consistently organised. So far, though, you haven't answered all the questions I asked.

[chinchee]

Hi macropod,

My apologies for the incomplete reply. Was trying to multitask and clearly it is not working.

Anyway:
1. Are the cells you want to copy always in the same place in the table?
Yes

2. Is there more than one table in the document?
Yes

3. If so, how is your table to be identified?
The table are just plain tables in Words with specific headers. I
presume that would be how I would identify the table. Do I get your
meaning correctly?

[macropod]

Re 3: How is the code to recognize the table? VBA can find tables easily enough, but how would it know then it's found the correct table? And, given that there's more than one table in the document, are the data in every table, or just one/some?

[chinchee]

Hi macropod,

I had a go at trying to record a macro to see how it goes. Luckily for me, the data will be in a template word document with several tables. The exact "location" of the data that I need is always in the same cell for all documents. I was thinking to simply move the cursor to the specific location and select everything in the cell, paste it into excel. This is just what I thought and I think it is very very crude. Perhaps you have a better idea? Please let me know what you think.

Thank you.

[macropod]

You still haven't answered the question from my last post!

And without seeing your code I can't comment on it. That said, anything produced by the macro recorder tends to be pretty crude, inflexible and useless for automating another application.

[chinchee]

Hi macropod,

I think I have answered you question?

How is the code to recognize the table?
Like I said - I was just planning on using a really crude macro something like the following:

Sub Macro1()
'
' Macro1 Macro
'
'
Selection.MoveDown Unit:=wdLine, Count:=4
Selection.MoveUp Unit:=wdLine, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=3
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=19, Extend:=wdExtend
Selection.Copy
End Sub

VBA can find tables easily enough, but how would it know then it's found the correct table? And, given that there's more than one table in the document, are the data in every table, or just one/some?
As I said, the datas are in exact "location" for every document, so I think the above macro should be alright - but I do agree with you that it is very crude

I must admit that I am a begineer to VBA, I am still trying to find my way around. Please bear with me if I sound like a total idiot.

[macropod]

As I said, the datas are in exact "location" for every document, so I think the above macro should be alright - but I do agree with you that it is very crude

That isn't what you said:

The exact "location" of the data that I need is always in the same cell for all documents.

and neither version means anything when it come to macro coding. How does a macro know which 'exact location' the table(s) are in? Remember, I can't see your document and neither can a macro.

[chinchee]

Hi macropod,

I think I am not explaning very well. My apologies.

Perhaps please take a look at the attached files. I hope they explain better. I have prepared two words document. I have like 100++ of them - for confidential reason, I can't show you the actual file I am sorry. I need to copy from the words file (Things that I need are highlighted in red/blue boxes), and I need to put them into the Excel file.

I hope this explains better what I am trying to do.

[macropod]

So, is the table you want to get the red data from always the first table in the document? Or could the table be somewhere else and the macro has to find the table?

[chinchee]

Yes, the red data will always be from the first table. Likewise, the blue data will always be from the second table.

[macropod]

I see from your speadsheet you want the data from two tables. You did not say that before. So, is the table you want to get the blue data from always the second table in the document?

Also, your tables have multiple rows. Do you want only the second row from each table, or do you want the other rows as well?

Finally, your Excel workbook only has one destination cell for each table row, but the tables have three cells per row. Your Excel worksheet should also have 3 cells per table row.

[chinchee]

Hi macropod,

So, is the table you want to get the blue data from always the second table in the document?
Yes.

Also, your tables have multiple rows. Do you want only the second row from each table, or do you want the other rows as well?
Yes only the specific row that I have highlighted using red/blue box.

Finally, your Excel workbook only has one destination cell for each table row, but the tables have three cells per row. Your Excel worksheet should also have 3 cells per table row.
To make things clearer, I think I should have specify how the data should appear in Excel - which I have done now. Please see the attached spreadsheet.

Thanks.