Extract text from certain cell in table

[newbie101]

Hi Wizards,

I'm trying to retrieve text from a cell in a table, something along these lines


Public Function fRetrieveCellText(intColumn As Integer, intCell As Integer) As String
' What goes here?
End Function

EDIT: I need the function to work on tables with merged cells too.

[fumei]

What have you tried?

BTW: you will have difficulty with merged cells.

[newbie101]

Public Function fRetrieveCellText(intRow As Integer, intCell As Integer) As String
fRetrieveCellText = Left(ActiveDocument.Tables(1).Rows(intRow).Cells(intCell).Range.Text, Len(ActiveDocument.Tables(1).Rows(intRow).Cells(intCell).Range.Text) - 2)
End Function

[gmaxey]

Sub Demo()
'Pass the cell to First Function
MsgBox fRetrieveCellText1(ActiveDocument.Tables(1).Cell(1, 1))

'Pass the range to Second Function
MsgBox fRetrieveCellText2(ActiveDocument.Tables(1).Cell(1, 1).Range)
End Sub


Public Function fRetrieveCellText1(ByRef oCell As Word.Cell) As String
fRetrieveCellText1 = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
End Function


Public Function fRetrieveCellText2(ByRef oRng As Word.Range) As String
'Comment the next line to try the bottom two lines
fRetrieveCellText2 = Left(oRng.Text, Len(oRng.Text) - 2)

'Or Uncomment the next two lines to try them.
'oRng.End = oRng.End - 1
' fRetrieveCellText2 = oRng.Text
End Function

[newbie101]

Greg thanks, but that gives two message boxes with all the text in the table.

[SamT]

Of course you get two message boxes, that is what Sub "Demo" calls for. I slightly edited GMaxey's code for better understanding. Look at it now.

As for returning the entire text of the Table, that might be because you have merged Cells in it. Especially if Cell (1, 1), the upper left cell is merged.

That being said, the second Function would have returned all the text as it was originally written. Try the code now.

If the first Message Box still shows all the text, try changing the Cell assignments in Sub "Demo" to an unmerged cell.

His second Functions shows two different ways to work with Text.

[fumei]

Like I stated, merged cells cause great difficulty.

[gmaxey]

Sam, Gerry;

I realize that merged cells can sometimes be problematic, but I'm not seeing it in this case.

I've run the following code on five tables

1. No merged cells
2. Cells 1, 1 and Cells 1, 2 merged
3. Cells 1, 1 and Cells 2, 1 merged
4. Cellss 1, 1/1, 2/2, 1 and 2, 2 merged
5. Unrelated cells merged

The code runs and returns the content of Cell 1,1 each time:


Sub DemoMethods()
'Passing cell
MsgBox fcnGetCellText1(ActiveDocument.Tables(1).Cell(1, 1))
MsgBox fcnGetCellText2(ActiveDocument.Tables(1).Cell(1, 1))
MsgBox fcnGetCellText3(ActiveDocument.Tables(1).Cell(1, 1))
'Passing range.
MsgBox fcnGetCellText4(ActiveDocument.Tables(1).Cell(1, 1).Range)
MsgBox fcnGetCellText5(ActiveDocument.Tables(1).Cell(1, 1).Range)
End Sub
Function fcnGetCellText1(ByRef oCell As Word.Cell) As String
'The range.text property includes the end of cell marker character which is a single character consisting of _
ChrW(13) & ChrW(7). It has a length = 2
fcnGetCellText1 = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
End Function
Function fcnGetCellText2(ByRef oCell As Word.Cell) As String
Dim oRng As Word.Range
Set oRng = oCell.Range
'The end of cell markers appears and is treated as a single character.
oRng.MoveEnd wdCharacter, -1
fcnGetCellText2 = oRng.Text
End Function
Function fcnGetCellText3(ByRef oCell As Word.Cell) As String
'Replace the end of cell marker with a null string.
fcnGetCellText3 = Replace(oCell.Range.Text, ChrW(13) & ChrW(7), vbNullString)
End Function
Function fcnGetCellText4(ByRef oRng As Word.Range) As String
oRng.End = oRng.End - 1
fcnGetCellText4 = oRng.Text
End Function
Function fcnGetCellText5(ByRef oRng As Word.Range) As String
oRng.Collapse wdCollapseStart
'Expand the range to the paragraph mark (the first part of the the ChrW(13) & ChrW(7) end of cell mark)
oRng.Expand
fcnGetCellText5 = oRng.Text
End Function

[macropod]

The real issue with merged/split cells is knowing what row/column references to use. Unlike Excel, where queries on a merged range can use the addresses of any of the merged cells, in Word the only valid address is that of the nominal top-left cell in the merged range. With split cells, Word interpolates however many rows/columns the split implies, effectively inferring that the adjoining cells are merged. I suspect this approach is needed because Word supports split cells, but Excel doesn't.

[SamT]

gmaxey,

The OP's complaint was that he got two message boxes, which is why I added some white space to your code.

In the second function in that original example
Public Function fRetrieveCellText2(ByRef oRng As Word.Range) As String
fRetrieveCellText2 = Left(oRng.Text, Len(oRng.Text) - 2)

oRng.End = oRng.End - 1
fRetrieveCellText2 = oRng.Text
End Function

The lines
oRng.End = oRng.End - 1
fRetrieveCellText2 = oRng.Text
Would override the first line, which is why I commented them out and suggested trying both methods separately.

[SamT]

Paul,

gmaxey's last post seems to contradict your statement "in Word the only valid address is that of the nominal top-left cell in the merged range"

I can't comment on that because I don't code in Word all that much.

In Excel, you can refer by address to any cell in a merged range without error, however only the upper left cell can contain any data.

In A1 enter "A", in B1 = "B", and C1 = "C"

Merge "A1" to "C1"

Watch X in this Sub. There will be no errors.

Sub tst()
Dim X

X = Range("A1")

Range("B1") = "BAH!"
X = Range("B1")
X = Range("A1")

X = Range("C1")

Range("A1") = "Aha!"
X = Range("A1")

Range("D1").Offset(0, -1).Select
Selection = "Again?"
X = Selection.Range("A1")

Range("B1").Select
Selection = "Bravo"
X = Selection.Value

MsgBox "Range A1 = " & X(1, 1)
MsgBox "Range B1 = " & X(1, 2)
MsgBox "Range C1 = " & X(1, 3)

End Sub



Now try this one

Sub tst2()
Dim X As String

Range("A1").Select
X = Selection.Value
End Sub

[fumei]

Actually Greg's post does not contradict Paul's post as greg's code only uses a reference to cell(1,1). See below.

Greg, as Paul wrote:
in Word the only valid address is that of the nominal top-left cell in the merged range
Therefore of course an explicit cell(1,1) reference will always work. Merged or not, calling it will return it as there is no other nominal top-left cell. Your code will also return values from merged cells...but...as Paul mentions you have to be careful with what row\column reference to use. It is sometimes not as obvious as we may like.

[SamT]

I was thinking of Paul's Post # 8, where he has three three tables with merged cells..

What am I missing?

[macropod]

Paul,

gmaxey's last post seems to contradict your statement "in Word the only valid address is that of the nominal top-left cell in the merged range"

I can't comment on that because I don't code in Word all that much.
Then, instead of suggesting a contradiction, perhaps you should try running the code to return the content from merged/split cells other than at 1,1. For example, create a multi-column, multi-row table with a variety of vertically-merge, horizontally-merged, vertically-split and horizontally-split cells, then try to retrieve every cell's content. You'll soon see what happens.

Your Excel demonstration is completely irrelevant - Word is not Excel and this is a discussion about Word tables.

I was thinking of Paul's Post # 8, where he has three three tables with merged cells..
My post has no tables.

[SamT]

Paul, I did not mean in any way to offend you. I apologize if I did.

I just wanted clarification.

[macropod]

No offense taken - I just happen to think it's silly to question something that, by your own admission, you're not truly conversant with.

[fumei]

Gentlemen, please.

Technically speaking it may better to think of the reference point of merged cells as the LOWEST number of its Range.Cells property, rather than the X,Y of its rows and columns. When cells are merged the resultant cell takes as its Range.Cells value the lowest number of the originals. I am not sure why Range.Cells is not used more. It is a single number independent of the X,Y co-ordinate system, as it is simply the COUNT of cells starting from top-left. True, XY has huge advantages as traditionally we think of tables as rows and columns (which of course they are).

BTW in my experience vertically merged cells have the most difficulty.

It is always dangerous to make direct comparisons between Excel and Word. They are conceptually radically different.

[SamT]

Actually Greg's post does not contradict Paul's post as greg's code only uses a reference to cell(1,1). See below.

Greg, as Paul wrote:
Therefore of course an explicit cell(1,1) reference will always work. Merged or not, calling it will return it as there is no other nominal top-left cell. Your code will also return values from merged cells...but...as Paul mentions you have to be careful with what row\column reference to use. It is sometimes not as obvious as we may like.

Thank you. While I caught that fact in Greg's first post, somehow, I developed temporary blindness with his second one.