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.
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
[Fmr MS MVP - Word]
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.
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
[Fmr MS MVP - Word]
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.
The document screenshot you posted is nothing like the table structure indicated in Post 1...
Cheers
Paul Edstein
[Fmr MS MVP - Word]
I tried with this table and unfortunately it didn't output, I don't get what I'm doing wrong
Screenshot1.JPG
Screenshot2.JPG
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.
Cheers
Paul Edstein
[Fmr MS MVP - Word]
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?
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
[Fmr MS MVP - Word]
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
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
[Fmr MS MVP - Word]
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
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
[Fmr MS MVP - Word]
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
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
[Fmr MS MVP - Word]
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.
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.
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
Hello,
Just for the sake of it, is there any possibility to make it find the exact SearchWord, or at least Case sensitive ?
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 StringDim 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
Many thanks
Best,
John