How to count the cells in a table row

[Alex St-Pierre]

Hi,
Does anyone know how to count the cells inside a table row ?
I already tried the following but, (because of merged cells), doesn't work
all the time. Is there an other way to count the cells in a row?

The following two methods doesn't work all the time:
iCount = Tables(1).Rows(1).Cells.Count
or
Dim oCell as Cell
Set oCell = Tables(1).Cells(1,1)
Do Until oCell.RowIndex = 4
oCell.Next
iCount = iCount + 1
Loop

 

[Tony Jollans]

Hi Alex,

I'm not entirely sure what you want from - or what goes wrong with - your
two code samples but I think the only guaranteed way is to iterate the cells
....

For Each OneCell In ActiveDocument.Tables(1).Range.Cells
If OneCell.RowIndex = 4 Then iCount = iCount + 1
Next

 

[macropod]

Hi Alex,

The following code returns the number of cells on each row in the first
table of the active document.

Some important points to note:
1. Merged cells on the same row count as one cell
2. Merged cells spanning two rows are only counted on the upper-most row
3. Vertically-split cells count as two cells AND cause Word to treat that
row as being two rows
4. Horizontally-split cells count as two cells

Sub Test()
Dim i As Integer
With ActiveDocument.Tables(1)
For i = 1 To .Rows.Count
On Error Resume Next
.Cell(i, 1).Select
Selection.SelectRow
MsgBox "Row " & i & " has " & Selection.Cells.Count & " Cells"
Next
End With
End Sub

Cheers

 

[Tony Jollans]

Hi macropod,

If you have vertically merged cells in the first column you get incorrect
results with that code. Depending on what else you have some cells may get
counted twice, some not at all.

 

[Alex St-Pierre]

Hi!
I tried the code you give me and it gives the same wrong message. If I
create a table in Word and merge the cells, all works good. If I create a
table in Excel and copy it to Word, it still work if I set "RTF:=False". When
I set "RTF:=True", Word is unable to count how many cells are in a merged
row. The problem is easy to reproduce as shown below. One thing is very
strange. If you click F5 to execute the programmation below, the msgbox shown
(Row 2 has 3 Cells). If you go step by step, the msgbox shown (Row 2 has 2
Cells)

Create an empty Word file and Excel file. Merge cells A2 and B2 in excel.
Then, use the programmation below in Word (using Excel Object Library).
Sub Test()
Dim i As Integer
Dim appWord As Word.Application
Dim appExcel As Excel.Application
Dim docWord As Word.Document
Dim wbExcel As Excel.Workbook
Dim rngExcel As Excel.Range
Dim pathExcel As String

Set appWord = Word.Application
Set docWord = appWord.ActiveDocument
Set appExcel = GetObject(, "Excel.Application")
Set wbExcel = appExcel.ActiveWorkbook
Set rngExcel = wbExcel.Application.ActiveSheet.Range("A1:C3")
rngExcel.Copy
Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False,
RTF:=True
With ActiveDocument.Tables(1)
For i = 1 To .Rows.Count
On Error Resume Next
.Cell(i, 1).Select
Selection.SelectRow
MsgBox "Row " & i & " has " & Selection.Cells.Count & " Cells"
Next
End With
End Sub

 

[Tony Jollans]

The problem would seem to be how Paste works. The table pasted as RTF *does*
have three cells in row 2.

--
Enjoy,
Tony

Hi!
I tried the code you give me and it gives the same wrong message. If I
create a table in Word and merge the cells, all works good. If I create a
table in Excel and copy it to Word, it still work if I set "RTF:=False". When
I set "RTF:=True", Word is unable to count how many cells are in a merged
row. The problem is easy to reproduce as shown below. One thing is very
strange. If you click F5 to execute the programmation below, the msgbox shown
(Row 2 has 3 Cells). If you go step by step, the msgbox shown (Row 2 has 2
Cells)

Create an empty Word file and Excel file. Merge cells A2 and B2 in excel.
Then, use the programmation below in Word (using Excel Object Library).
Sub Test()
Dim i As Integer
Dim appWord As Word.Application
Dim appExcel As Excel.Application
Dim docWord As Word.Document
Dim wbExcel As Excel.Workbook
Dim rngExcel As Excel.Range
Dim pathExcel As String

Set appWord = Word.Application
Set docWord = appWord.ActiveDocument
Set appExcel = GetObject(, "Excel.Application")
Set wbExcel = appExcel.ActiveWorkbook
Set rngExcel = wbExcel.Application.ActiveSheet.Range("A1:C3")
rngExcel.Copy
Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False,
RTF:=True
With ActiveDocument.Tables(1)
For i = 1 To .Rows.Count
On Error Resume Next
.Cell(i, 1).Select
Selection.SelectRow
MsgBox "Row " & i & " has " & Selection.Cells.Count & " Cells"
Next
End With
End Sub

 

[Tony Jollans]

Actually, I have just had a quick look at the RTF which Excel creates and,
although I can't read it properly, I think it's the Excel Copy operation
which is at fault.

 

[Alex St-Pierre]

I'm not sure if it has 3 cells in a row because I see only 2 and if you
execute the program step by step, you will see (with msgbox) that there are
only 2 cells in
row 2 as expected. It strange that F5 and step by step doesn't give same
answer ?

 

[Alex St-Pierre]

Yes, I saw that too.. as example, the LineWidth or the merged cell was equal
to:
-2079342459 and gives me error message 5843 when I tried to change it.
I posted this problem in the issue ("Run time error 5843").

 

[Tony Jollans]

You only see two cells because there is no border between them - if you tab
through them (or display formatting marks) you'll see that there are three.

I can't reproduce your other problems - different behaviour with F5 and F8,
or the error with borders.

 

[Alex St-Pierre]

There are not 3 cells in row 2 on my computer. If I use tabulation or display
formatting marks, it shown me that there are only 2. I can write text until
the end of the cell (2 rows width). I don't know why word doesn't merge your
cell in row 2 ? If you try to add text in excel that takes 2 columns space,
maybe Word should merged the cells ?

On my computer, Word merge cells:
1- If the merged cells is defined in excel.
2- If the text go on an other empty column.

If I use Rtf:=False or Rtf:=True, both gives the same table with a real
merged cells.
This is why I can see: Row 2 has 2 Cells in step by step.

I'm using Office XP SP3. I have the same problem with Office 2003.

 

[Tony Jollans]

At the moment, then, I'm clean out of ideas and can't reproduce anything.
Sorry.

 

[Alex St-Pierre]

Hi Tony,
I have search on MS web site but doesn't find informations. Yesterday, I
have reproduce the problem with three computers. Can I ask Microsoft if it's
a bug or not because it seems that MS has already had problem with Pasting
Excel Worksheet in Word. See this article:
WD2002: Rows or Columns Are Missing from a Pasted Microsoft Excel Worksheet
(happens with RTF file and corrected in SP3)
Thanks !
Alex

 

[Alex St-Pierre]

Hi Tony,

The error with borders is complicated to reproduce. It doesn't word in this
sample case. The error with merged cells seems to be simpler to reproduce. I
don't understand why you don't have merged cells in word table as results ?

The reason why I use Rtf option is because it's the only option I found to
avoid corruption of my Word document. I have a Word document that has 20
pages. If I copy the table inside this document with Rtf:=False, I can work
in that document but can't save it (takes about 15 minutes to save). If I use
Rtf:=True.. takes 1 sec. to save. After the copy of the table in word, even
if I delete all the document, I can't save it. I don't know what Rtf:=False
is doing on my document. If I create a new document, I can copy table with
Rtf:=False and save it without problem.
If you want, I can send you the word and excel file to see what is wrong.

Thank you !

 

[macropod]

Hi Tony,

The behaviour here is just an extension of (3), with all the cells on the
rows spanned by the merged cells being counted each time.

Greg Maxey has some code that may be useful at:
http://gregmaxey.mvps.org/Table_Cell_Data.htm

Cheers

 

[Alex St-Pierre]

Hi Macropod,

Finally, I learned that my word file I am working on has been created with
Office 95. So, what I did is copy all section without section break in a new
(fresh) document. After doing that, I'm able to copy the table from excel to
word in my new document and save it without having to use the Rtf option. All
works good now with merged cells.!!

Thanks
--
Alex St-Pierre


"macropod" a écrit :

Hi Tony,

The behaviour here is just an extension of (3), with all the cells on the
rows spanned by the merged cells being counted each time.

Greg Maxey has some code that may be useful at:
http://gregmaxey.mvps.org/Table_Cell_Data.htm

Cheers

 

[macropod]

Hi Alex,

I'm glad, and I'm sure you're relieved.

Cheers