Word table cell marking routine needed

[Bill Planey]

Hello,

I have a thorny problem that I am hoping for a creative solution to - I have
scripts that convert Word tables to tab-separated text, but because some
cells are (intentionally) left empty in the source document, I need a way to
mark them before they are collapsed so that in their collapsed state there
are not two consecutive tabs left behind, rather, a tab followed by a
placeholder string of my own devising, followed by the second tab. My script
consolidates consecutive tabs into a single tab later (because the vast
majority of consecutive tabs are truly unnecessary). The only way to
properly re-constitute the table that has an intentionally empty cell in a
number column is to mark the cell before hand with a placeholder string.

Sometimes, a cell directly above or below such cells will have a value in
it, so there is the possibility to script something that takes that into
account. But this is not always the case. I suppose a more reliable
scenario would be to check if ANY other cells in the same column have values
in them, which would validate leaving a placeholder in that particular empty
cell.

But what if the column has rows running through it that contain merged
cells? How can you truly automate this if there are so many potential forms
of interference with a clear-cut approach?

I don't know if this newsgroup allows attachments, so I have not included
one. But if requested, I'll post again with a sample table that has a
yellow cell that illustrates my dilemma.

BTW, my solution will be VBA code executed via AppleScript.

Many thanks in advance,

Bill Planey

 

[John McGhie]

Hi Bill:

This is not a "huge" problem if you are a strong coder, and it sounds as if
you are. But it's a bugger to work it out, because it's non-intuitive.

Sub EmptyCells()
' Marks empty cells

Dim aTable As Table
Dim aCell As Cell

Set aTable = Selection.Tables(1)

For Each aCell In aTable.Range.Cells
If Len(aCell.Range.Text) < 3 Then
aCell.Range.Text = "<EmptyCell>"
End If
Next ' aCell

Set aTable = Nothing
End Sub

The easiest way to find an "empty" cell is to check the length of the text
range. A table cell will always return two characters: a blank for the text
followed by the end-of-cell marker, which has a different character number.
VBA string handling won't handle Unicode, so you cannot easily return the
character code for the end of cell marker. However, Length < 3 MUST be
empty

Note that I have returned each cell from the table RANGE. This is a
work-around: VBA does not enumerate the cells of a table as a
directly-addressable collection; furthermore, if a table has merged cells,
neither the row nor column collections can be accessed.

Let me know if you need any more information.

Hello,

I have a thorny problem that I am hoping for a creative solution to - I have
scripts that convert Word tables to tab-separated text, but because some
cells are (intentionally) left empty in the source document, I need a way to
mark them before they are collapsed so that in their collapsed state there
are not two consecutive tabs left behind, rather, a tab followed by a
placeholder string of my own devising, followed by the second tab. My script
consolidates consecutive tabs into a single tab later (because the vast
majority of consecutive tabs are truly unnecessary). The only way to
properly re-constitute the table that has an intentionally empty cell in a
number column is to mark the cell before hand with a placeholder string.

Sometimes, a cell directly above or below such cells will have a value in
it, so there is the possibility to script something that takes that into
account. But this is not always the case. I suppose a more reliable
scenario would be to check if ANY other cells in the same column have values
in them, which would validate leaving a placeholder in that particular empty
cell.

But what if the column has rows running through it that contain merged
cells? How can you truly automate this if there are so many potential forms
of interference with a clear-cut approach?

I don't know if this newsgroup allows attachments, so I have not included
one. But if requested, I'll post again with a sample table that has a
yellow cell that illustrates my dilemma.

BTW, my solution will be VBA code executed via AppleScript.

Many thanks in advance,

Bill Planey

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Consultant Technical Writer
Sydney, Australia +61 4 1209 1410

 

[John McGhie]

Hi Bill:

Your request is perfectly possible (it's not even difficult, in fact). Word
VBA is very, very powerful (and so, by extension, is its AppleScript).

The trick was building a routine that will visit every cell in a table: once
you have that, it's a trivial matter to check the column with of the cell
you are in.

Sub EmptyCells()
' Marks empty cells

Dim aTable As Table
Dim aCell As Cell

Set aTable = Selection.Tables(1)

For Each aCell In aTable.Range.Cells
With aCell
If Len(.Range.Text) < 3 And .Width > 40 Then
.Range.Text = "<EmptyCell>"
End If
End With
Next ' aCell

Set aTable = Nothing
End Sub

The above code will operate only in cells wider than 40 points.

You will see that I have added a With statement in an attempt to improve the
performance of the macro. It didn't work: the thing runs like molasses in
winter, and I am trying to find out why: it's a bug in Word 2004.

Cheers

Hi John,

Thanks for this idea. It certainly marks empty cells, but unfortunately,
there is an additional wrinkle. Sometimes the creators of such tables add a
column of very narrow cells just for horizontal spacing; sometimes such a
narrow column is there for the occasional use of a footnote or currency
symbol... These empty cells are NOT the kind I want to save and therefore do
not need to be marked. But somehow, they need to be distinguished from the
empty cells that share a column with cells on other rows that DO have a value.

Let me try to construct a simple example using the monospaced Courier font:

Description Phrase1 $1,000.00 $ 900.00 $ 80.00
Description Phrase2 2,000.00 91.00
Description Phrase3 3,000.00 400.00 50.00

The cell that needs to be marked by the subroutine is the one in the second
row, below the $900.00 value and above the 400.00 value. Now, pretend that
the dollar signs are actually in separate cells from the numbers next to them;
this technique is often used by those who set up such tables (either in Word
or Excel) in order to keep the dollar signs vertically aligned. There is
therefore a thin column of cells which sometimes contain dollar signs, and
sometimes are left empty. THESE instances of empty cells should be ignored by
the subroutine; only the cell on row 2 (in this example) should get the marker
so that later it can be removed and the two consecutive tabs that push the
91.00 figure out to the last column survive and the value remains in the
correct location.

I suppose if the subroutine could evaluate the WITDH of an empty cell and only
mark the largest of those that occur on rows that already contain other
values, it could then intelligently decide which empty cells are appropriately
marked and which should be left unmarked.

Does this approach impossibility?

Thanks,

Bill Planey

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Consultant Technical Writer
Sydney, Australia +61 4 1209 1410

 

[Bill Planey]

John,

Yes, this works, and it is very slow in both Word 2004 and Word X. I am
sure it just happens in a snap in the Windows version (I am Mac-only, cannot
test)...

I appreciate your help in this and I'm sure there is a speedier approach,
either using VBA or something else. I'll keep an eye on these newsgroups to
see if something materializes. I think I have a potential solution using
FileMaker Pro.

Thanks!

 

[John McGhie]

Hi Bill:

We spent some time playing around with it, and were unable to produce the
significant speed improvement we were hoping for.

Here's our latest attempt:

Sub EmptyCells()
' Marks empty cells

Dim aTable As Table
Dim myCells As New Collection
Dim aCell As Cell

Application.ScreenUpdating = False

Set aTable = Selection.Tables(1)
For Each aCell In aTable.Range.Cells
myCells.Add aCell
Next

For Each aCell In myCells
With aCell
If .Width > 40 Then
If Len(.Range.Text) < 3 Then
.Range.Text = "<EmptyCell>"
End If
End If
End With
Next ' aCell

Set aTable = Nothing
Set myCells = Nothing

End Sub

That will be ten times faster than the previous attempt, but it's still not
"quick".

The sorry tale seems to be that Mac Word VBA is enumerating the entire
collection of table cells on each iteration. The above code pulls every
trick in the book to avoid that, with very limited success.

Turning ScreenUpdating off speeds most things up in Word by saving the
horsepower used to update the display: but it does mean nothing appears to
be happening while the macro is running.

Run this thing on a savagely powerful machine with lotsa real memory and no
other applications running

Cheers

John,

Yes, this works, and it is very slow in both Word 2004 and Word X. I am
sure it just happens in a snap in the Windows version (I am Mac-only, cannot
test)...

I appreciate your help in this and I'm sure there is a speedier approach,
either using VBA or something else. I'll keep an eye on these newsgroups to
see if something materializes. I think I have a potential solution using
FileMaker Pro.

Thanks!

--

Please reply to the newsgroup to maintain the thread. Please do not email
me unless I ask you to.

John McGhie <[email protected]>
Consultant Technical Writer
Sydney, Australia +61 4 1209 1410