Copying Word cell contents using find and tab but what if the cell is empty?

[par4724]

Hello, does anybody know of another way of copying table cell data? I need to copy cell data from a document to a report document and the way that I have done this so far is to do a find for i.e. "Employee Name" in the document, tab right to the next cell, copy that cell and then activated the report document and paste it into the "Employee Name" cell in the report but I have hit a snag. If the contents of the table cell is empty it throws out the whole macro as it skips to the next cell. Can anybody help? Is there an easier method, i.e. a table cell ref? I am using Word 2003.

The code that I have so far is here: -


With Selection.Find
.Text = "Employee Name"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCell
Selection.Copy

Thanks, Paul.

Paul, select your code and hit the vba button to format it for the forum.

[fumei]

More specific details would be needed, but in general avoid using Selection.

1. "to do a find for i.e. "Employee Name" in the document, tab right to the next cell,"

Does that mean "Employee Name" will NEVER be in the document except in a table? Do you, perhaps only want to search through tables, ignoring the rest of the document?

2. " Is there an easier method, i.e. a table cell ref? "

Indeed. Yes, you can make a table cell reference. Use Cell (and Table, and Document) objects.
Option Explicit

Sub GetNames()
Dim oTable As Table
Dim oCell_A As Cell
Dim oCell_B As Cell
Dim ThisDoc As Document
Dim ThatDoc As Document
Dim strName As String

' set current doc object
Set ThisDoc = ActiveDocument
' set table object
Set oTable = ThisDoc.Tables(1)
' set new doc object
Set ThatDoc = Documents.Add

' for each cell in table object
For Each oCell_A In oTable.Range.Cells
' if the cell has Employee Name
If InStr(oCell_A.Range.Text, "Employee Name") > 0 Then
' make second cell object the cell beside it
Set oCell_B = oTable.Cell(oCell_A.RowIndex, _
oCell_A.ColumnIndex + 1)
' if THAT cell is not "empty"
If Len(oCell_B.Range.Text) > 2 Then
' put its text into string variable
strName = oCell_B.Range.Text
' and add it to the new doc
ThatDoc.Range.InsertAfter strName
End If
End If
Next
End Sub
Demo attached. Click Get Names on top toolbar.

The code takes the names from each cell with a name. Note that the table has both cells with "Employee Name" but the second cell is blank, AND rows with no "Employee Name".

ONLY the cells with a name are carried over to a new document. Also note that no copying and pasting is required.

The code only processes through Table(1). That is why I mention more specifc details may be required. Do you need to process more than one table, the whole document?

Note also that the code will NOT work if you have any merged cells - which hopefully you do not.

Finally, the cell to grab the names from is hard-coded - the cell one column over (oCell_A.ColumnIndex + 1) from the one that has "Employee Name" .

[par4724]

Hi Fumei, Thanks for your prompt reply and code example. That's precisely what I wanted. That would have taken me weeks as I am learning as I go along!

Yes, there will be more than one table so at a rough quess, so that I don't keep you waiting, would that mean that I can reference each table i.e. Set oTable = ThisDoc.Tables(1), tables(2), etc or would I need to reference the tables another way?

Thanks.

[fumei]

No, your code is incorrect. You can only Set one object at a time. However, you can declare an object, then use a For Each...statement.
Option Explicit

Sub GetNames()
Dim oTable As Table
Dim oCell_A As Cell
Dim oCell_B As Cell
Dim ThisDoc As Document
Dim ThatDoc As Document
Dim strName As String

Set ThisDoc = ActiveDocument
Set ThatDoc = Documents.Add
For Each oTable In ThisDoc.Tables
For Each oCell_A In oTable.Range.Cells
If InStr(oCell_A.Range.Text, "Employee Name") > 0 Then
Set oCell_B = oTable.Cell(oCell_A.RowIndex, _
oCell_A.ColumnIndex + 1)
If Len(oCell_B.Range.Text) > 2 Then
strName = oCell_B.Range.Text
ThatDoc.Range.InsertAfter strName
End If
End If
Next
Next
End Sub
Notice that there is no explicit set instruction for oTable - unlike the previous code.

For Each... is an implicit setting of the table object.

In other words, VBA "set"s the object implicitly - "for every table object in ActiveDocument.Tables use oTable as that object".

This is the same as my use of oCell_A.For Each oCell_A....
The declared object - Dim oCell_A As Cell - is implicitly Set by the use of For Each...

It is used to loop through ALL cell objects in the collection (For Each). Just like oTable is used to loop through ALL table objects in the table collection.

When you are making a declared object a specific object, then you use Set. Notice I still use:
Set oCell_B = oTable.Cell(oCell_A.RowIndex, _
oCell_A.ColumnIndex + 1)
as it is being Set to a specific cell.

[par4724]

Hi Fumei,

Thanks for your help and for explaining it so well. I will continue working on my document with the help of your code with the addition of the For Each statement.

Cheers,
Paul.