Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2014, 12:06 PM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default Copy data from Word into Excel

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.
Attached Files
File Type: docx EXAMPLE.docx (12.5 KB, 9 views)
File Type: xlsx EXAMPLE.xlsx (7.9 KB, 8 views)
Reply With Quote
  #2  
Old 05-21-2014, 03:17 PM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-22-2014, 05:53 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 05-22-2014, 06:02 AM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-22-2014, 07:00 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 05-22-2014, 03:49 PM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 05-23-2014, 12:58 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 05-23-2014, 02:45 AM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 05-23-2014, 03:12 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 05-23-2014, 03:22 AM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
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:
Quote:
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 05-23-2014, 03:48 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

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.
Attached Files
File Type: docx ExampleDocument1.docx (17.4 KB, 8 views)
File Type: docx ExampleDocument2.docx (17.4 KB, 9 views)
File Type: xlsx ExampleExcel.xlsx (8.8 KB, 7 views)
Reply With Quote
  #12  
Old 05-23-2014, 03:53 AM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 05-23-2014, 03:57 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

Yes, the red data will always be from the first table. Likewise, the blue data will always be from the second table.
Reply With Quote
  #14  
Old 05-23-2014, 04:00 AM
macropod's Avatar
macropod macropod is offline Copy data from Word into Excel Windows 7 32bit Copy data from Word into Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 05-23-2014, 04:13 AM
chinchee chinchee is offline Copy data from Word into Excel Windows 7 64bit Copy data from Word into Excel Office 2010 64bit
Novice
Copy data from Word into Excel
 
Join Date: May 2014
Posts: 10
chinchee is on a distinguished road
Default

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.
Attached Files
File Type: xlsx ExampleExcel.xlsx (9.0 KB, 10 views)
Reply With Quote
Reply

Tags
copy/paste, multiple files, words

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Excel data into an existing Word Table JJG Word 1 12-18-2013 05:41 PM
How to Copy data from Outlook mail and Paste it in a Excel sheet? padhu1989 Outlook 0 09-11-2012 04:07 AM
how to copy all ms word tables into excel rehan129 Word 0 01-28-2012 10:17 AM
Copy data from Word into Excel How to copy automatically data from Excel file to Word file? fuchsd Word 6 10-25-2011 05:52 AM
Copy all comments & cell contents (i.e. data) to word? IanM Excel 0 07-03-2010 11:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft