Solved: Extract Selected Data from Word to Excel

[sheeeng]

Hi all,

I face problem while I want to extract selected data out from Word to Excel.

My word doc is below (contains a table)....

-------------------------------------
Student 1: Leonard Lee
Name Leonard
Gender Male
Age 20
Address Kuala Lumpur
Email leonardleeshengsheng@yahoo.com
Height 1.7
Weight 65
Comment Good Student
Type Pessimistic
-------------------------------------

This is the macro i had created in excel.......
[vba]Sub OpenAndReadWordDoc()
' assumes that the previous procedure has been executed
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
Workbooks.Add ' create a new workbook
With Range("A1")
.Formula = "Word Document Contents:"
.Font.Bold = True
.Font.Size = 14
.Offset(1, 0).Select
End With
r = 3 ' startrow for the copied text from the Word document
Set wrdApp = CreateObject("Word.Application")
'wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Open("D:\Profiles\stu678\Desktop\Data.doc")
' example word operations
With wrdDoc
For p = 1 To .Paragraphs.Count
Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _
End:=.Paragraphs(p).Range.End)
tString = tRange.Text
tString = Left(tString, Len(tString) - 1)
' exclude the paragraph-mark
' check if the text has the content you want
If InStr(1, tString, "1") > 0 Then
' fill into active worksheet
ActiveSheet.Range("A" & r).Formula = tString
r = r + 1
End If
Next p
.Close ' close the document
End With
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
End Sub[/vba]
I want the Name, Student 1, Gender, Height data only.....but i get below results........

Word Document Contents:
1.7

Please advise....

Thanks.

Leonard.

[Killian]

Hi Leonard and welcome to VBAX

I think you've got it about right, but if your text is in a table in Word, you can refer to it using the tables object
Also, your InStr check to see if the text has the content you want, tests for "1", which is why "1.7" is the only one that's returned.
So your, wrdDoc loop needs to be more like:[vba]With wrdDoc.Tables(1)
For r = 1 To .Rows.Count
tString = .Cell(r, 2).Range.Text
' exclude the paragraph-mark
tString = Left(tString, Len(tString) - 1)
ActiveSheet.Range("A" & r + 1).Value = tString
Next
End With
wrdDoc.Close[/vba]Of course, this is assuming we're dealing with table 1 and the text is all entered as expected, so you may need to add a few tests to make the loop more robust

[sheeeng]

Thanks. I'll try it now.

[sheeeng]

How do I make specific selection on data? For instance, I just want to extract data out for Name & Height only to a Word.

Please advise.

Thanks

[Killian]

Then you could use your InStr test on the text in the first column to see if the row qualifies[VBA]With wrdDoc.Tables(1)
For r = 1 To .Rows.Count
'check if col 1 is "Name" or "Height"
If InStr(1, .Cell(r, 1).Range.Text, "Name") Or InStr(1, .Cell(r, 1).Range.Text, "Height") Then
tString = .Cell(r, 2).Range.Text
' exclude the paragraph-mark
tString = Left(tString, Len(tString) - 1)
ActiveSheet.Range("A" & r + 1).Value = tString
End If
Next
End With
wrdDoc.Close[/VBA]

[hairywhiterabbit]

Hi Sheeeng,

Have you considered importing the data as a complete set from Word and then deleting the information you don't require from within Excel.

When moving data between Word tables and Excel, it is much quicker and more efficient to convert a table to text, set it to a variable (an array), edit the data to get rid of what you don't want and then set a range in Excel to that variable.

If you'd like some help with that gis a yell.

Cheers,
Andrew

[sheeeng]

Thanks