Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 42

Thread: [VBA Excel] To find word in Microsoft Word Table and copy Offsets to Excel Cells

  1. #21
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    It works for me... I suggest you check the cell relationships implied by post #1 - the code looks for 'Fruits' then retrieves whatever is one cell below and to the right of that.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  2. #22
    Hello Paul,
    Yes the .Text is correct I double checked that, the .Text is assigned correctly, and it appears in the Word Document (.docx) (In a Table), everything seems fine but the Excel remains blank. What cells should it fill ? I used the Code updated as in Post #17

    Best,
    John
    Last edited by johngalvin; 09-22-2019 at 10:22 PM.

  3. #23
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    You should get output with the filename in column A, then each table's result in columns B, C, etc. Each document's output starts on a new row. As written, the cells containg the output values must be one cell below and to the right of the cell containing 'Fruits' and must not contain any paragraph breaks before the one containing the output value.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  4. #24
    Hey Paul

    Thanks for your quick reply !

    Basically I have these tables in a Word Document, and one of it has the Word "Fruits" in it, unfortunately it doesen't output anything in Excel. I was thinking of a code that would search within the Word Document find the "Fruits" within the table and get the next column to a specific given Excel Cell, sort of what Dave has done, but it would be more suitable if it could be faster (because with Dave's code it works, but it's like 10-15 seconds /cell)


    This is an example of the Word Document:
    Screenshot1.JPG

    I tried to use Excel VBA to search for "Fruits" within the Word Document and it should output something like this (please note that I added the text there manually, in Excel as the VBA Code in post #17 won't output anything unfortunately)
    Screenshot2.JPG
    Last edited by johngalvin; 09-22-2019 at 11:28 PM.

  5. #25
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    The document screenshot you posted is nothing like the table structure indicated in Post 1...
    Cheers
    Paul Edstein
    [MS MVP - Word]

  6. #26
    I tried with this table and unfortunately it didn't output, I don't get what I'm doing wrong
    Screenshot1.JPG

    Screenshot2.JPG

  7. #27
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    The attached demonstrates both the kind of source documents the code expects and the output. Indeed, if you extract the files and run the macro, you should get new rows with the same output.
    Attached Files Attached Files
    Cheers
    Paul Edstein
    [MS MVP - Word]

  8. #28
    Hey Paul !

    Cool, cheers for the Demo. I get how it works now.
    Can this code be adjusted to put a specific cell from the Table to a specific Cell in Excel, rather than inserting in Columns?

    Here for example
    Capture.JPG
    To look for "Apples" and return 5 into cell A1 in Excel?

    I made it look for "Apples" in the 1st Word Document "Fruits1.docx", for the 1st Table, And this is the current Output:

    Captures.JPG
    The question is: can the output Cell be set-up from the Excel VBA to a given Cell?

  9. #29
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    Most of what you want is as simple as -
    1. Deleting:
    WkSht.Cells(r, c).Value = Split(strFile, ".docx")(0)
    2. Changing:
    .Text = "Fruits"
    to:
    .Text = "Apples"
    3. Changing:
    .Cells(1).RowIndex + 1
    to:
    .Cells(1).RowIndex
    Of course, the reason for capturing the filenames was so you could tell where the data originate.

    As for writing to a particular cell, what that risks is simply overwriting the values, which doesn't seem particularly useful to me...
    Cheers
    Paul Edstein
    [MS MVP - Word]

  10. #30
    Hey Paul,

    Yes, I've managed to change as you said, as it can be seen in post #28.
    The only thing I need now is to output to a Specific Cell, any idea how this can be done?

    Many thanks,
    John

  11. #31
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    So you don't care if evey table in every document simply overwrites what's already there, leaving you with only the last returned value? If so, what's the point of using a macro to loop through all the tables in all the documents???
    Cheers
    Paul Edstein
    [MS MVP - Word]

  12. #32
    Hi Paul,

    I do care, but what I'm trying to say is that it won't overwrite.
    For Example, as above in Post #28 it can have a Table with a single "Apples" entry, so it finds it and puts the cell from the next column (value=5) in this case to Excel on Cell A1 say. Then, can look for another keyword that appears also once for example:"Oranges" and it can put the cell from the next column (value=2 let's say) in Excel on Cell A2 let's say, it won't overlap since the tables are not having same keywords.

    Best,
    John

  13. #33
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    And if you open 20 files containing 30 tables with the word 'apples', you'll end up with only the 'apples' value from the last table in the last document!!!!! All the rest will have been overwritten.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  14. #34
    Yes, you are right here. But I also have 1 Word Document with Tables that don't have the same values in it, so it won't overwrite in the Excel. How should that output be done to fill in a specific Excel Cell? Can this be done?

    Best,
    John

  15. #35
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    If you only have one Word document, you really don't need code to go through a whole folder. Try:
    Sub GetTableData()
    'Note: this code requires a reference to the Word object model.
    'See under the VBE's Tools|References.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application, wdDoc As Word.Document, wdTbl As Word.Table
    Dim ArrFnd(), WkSht As Worksheet, i As Long
    ArrFnd = Array("Apples", "Pears", "Oranges")
    Set WkSht = ActiveSheet
    With wdApp
      With .Dialogs(wdDialogFileOpen)
        .Name = "*.doc"
        .ReadOnly = True
        If .Show = -1 Then
          Set wdDoc = wdApp.ActiveDocument
          With wdDoc
            For i = 0 To UBound(ArrFnd)
              For Each wdTbl In .Tables
                With wdTbl.Range
                  With .Find
                    .Text = ArrFnd(i)
                    .Wrap = wdFindStop
                    .Execute
                  End With
                  If .Find.Found = True Then
                    WkSht.Range("B" & i + 2).Value = Split(wdTbl.Cell(.Cells(1).RowIndex, .Cells(1).ColumnIndex + 1).Range.Text, vbCr)(0)
                    Exit For
                  End If
                End With
              Next
            Next
            .Close SaveChanges:=False
          End With
        Else
          MsgBox "No file selected. Exiting", vbExclamation
        End If
      End With
      .Quit
    End With
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    Last edited by macropod; 09-23-2019 at 06:04 AM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  16. #36
    Hey Paul,

    Cheers, that works exactly as suited for the 1 Document version.

    Thank you both !(Also to Dave)

    Best,
    John
    Last edited by johngalvin; 09-23-2019 at 03:14 AM.

  17. #37
    Paul,

    Would the code run faster in terms of Filling in the Excel Cells if the For loop would be replaced with a With in a table range ?
    It takes a bit, like few good minutes to fill in the Excel Cells, even if using Arrays, and the CPU is not bad, so was wondering if the fill-in speed could be adjusted to faster?

    Cheers

    Best,
    John
    Last edited by johngalvin; 09-23-2019 at 09:05 AM.

  18. #38
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    609
    Location
    John I see that Paul has provided you with some very nice code using an alternate approach. Your concern was the delay in removing the pilcrow. I think the delay is actually caused by the time it takes to close the doc and quit the Word App. U could trial adding some screen updating code so that U don't see the results with the pilcrow only the final output. HTH. Dave

  19. #39
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,888
    Location
    Quote Originally Posted by johngalvin View Post
    Would the code run faster in terms of Filling in the Excel Cells if the For loop would be replaced with a With in a table range ?
    Unlikely. Most of the time the macro takes to execute relates to creating the necessary Word session and opening the document. Once those overheads are dealt with, the rest of the code is very quick.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  20. #40
    Hello,

    Just for the sake of it, is there any possibility to make it find the exact SearchWord, or at least Case sensitive ?

    Dim SearchWord As String
    
    SearchWord = "Apple"
    
          
              For Each wdTbl In .Tables
                With wdTbl.Range
                  With .Find
                     
                    .Text = SearchWord
                    .Forward = True
                    .Wrap = wdFindStop
                    .Execute
                  End With
                  If .Find.Found = True Then
                    WkSht.Range("A" & 1).Value = Split(wdTbl.Cell(.Cells(1).RowIndex, .Cells(1).ColumnIndex + 1).Range.Text, vbCr)(0)
                    Exit For
                  End If
                End With
      
            Next
    At the moment, it just grabs the 1st Apple Word it encounters in the 1st table. Is there any possibility to grab the last Apple from the last Table? I tried with .Find and then .Forward = True but it still grabs the 1st encountered one and not the last. Is there any possibility to make it to find the exact word? I tried using String

    Many thanks
    Best,
    John

Posting Permissions

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