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.
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
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.
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:
Attachment 25131
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)
Attachment 25132
The document screenshot you posted is nothing like the table structure indicated in Post 1...
I tried with this table and unfortunately it didn't output, I don't get what I'm doing wrong
Attachment 25133
Attachment 25136
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.
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
Attachment 25139
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:
Attachment 25141
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...
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???
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.
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:
Code: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
Hey Paul,
Cheers, that works exactly as suited for the 1 Document version.
Thank you both !(Also to Dave)
Best,
John
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
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 StringCode: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
Many thanks
Best,
John