Consulting

Results 1 to 7 of 7

Thread: Solved: Extract Selected Data from Word to Excel

  1. #1
    VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location

    Exclamation Solved: Extract Selected Data from Word to Excel

    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.
    Last edited by Killian; 05-18-2005 at 02:14 AM. Reason: Formatting & VBA tags

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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
    K :-)

  3. #3
    VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Thanks. I'll try it now.

  4. #4
    VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    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

  5. #5
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    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]
    K :-)

  6. #6
    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

  7. #7
    VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •