Extraction

[Greg Maxey]

Hello,

I was reading a reply Cindy Meister provided a while back to another
question. In her reply she said that you could write a macro to extract the
first letter of a word in a list and create a new list.

Can this be extended to say the third and fourth character or the
information between slashes.
For example if I had a table with First Name, Last Name and Date,
Greg Maxey
9/14/03

How would one go about creating a macro to extract "G" and create an new
column Initial that contains "G" and extract "14" and create a new column
Day that contains "14."

Thank you.


--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[Mark Tangard]

Hi Greg,

You can do this straightforwardly once you understand the VBA
string-handling functions, which are admittedly intimidating
at first:

Dim t As Table, ro As Row
Set t = ActiveDocument.Tables(1)
For Each ro In t.Rows
ro.Cells(4).Range.Text = Left(ro.Cells(1).Range.Text, 1)
ro.Cells(5).Range.Text = Mid$(ro.Cells(3).Range.Text, _
InStr(ro.Cells(3).Range.Text, "/") + 1, _
InStr(Mid$(ro.Cells(3).Range.Text, _
InStr(ro.Cells(3).Range.Text, "/") + 1, 99), "/") - 1)
Next ro

The gooey part (the second statement of the loop) isn't nearly
as evil as it looks. It may be more comprehensible using a
With/EndWith block:

Dim t As Table, ro As Row
Set t = ActiveDocument.Tables(1)
For Each ro In t.Rows
ro.Cells(4).Range.Text = Left(ro.Cells(1).Range.Text, 1)
With ro.Cells(3).Range
ro.Cells(5).Range.Text = Mid$(.Text, InStr(.Text, "/") + 1, _
InStr(Mid$(.Text, InStr(.Text, "/") + 1, 99), "/") - 1)
End With
Next ro
End Sub

Of course, this sort of thing is more appropriate to Excel
and wouldn't require any VBA there either. On an Excel sheet
set up like this table, once the formulas are set up & copied
downward, the 2 columns that VBA is calculating here could be
produced "on the fly," as data is entered into the first 3.
And the formulas for those columns are almost comically simple
compared to the VBA code above.

 

[Greg Maxey]

Mark,

Thanks for the start. I sort of follow what happens. I notice it get
tangled up by the first row as the heading is Date and there is no "/." It
also tangles up if there are empty rows. I will play around and see what I
can do. Thank you.

--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[Mark Tangard]

Easy to fix. After the 'For' line, insert:

If InStr(ro.Cells(3).Range.Text, "/") = 0 Then GoTo SkipThisRow

and just before the 'Next' line, insert:

SkipThisRow:

 

[Greg Maxey]

Mark,

Interesting! A few questions:

What is the significance of "99" in the line before End With?

This macro requires columns 4 and 5 to be present. If I start out with a 3
column table I get a run time error 5941 "The requested member of the
collection does not exist." How would you instruct Word to create a column 4
and column 5 to hold the data created by the macro?

Thanks, Greg


--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[Mark Tangard]

Hi Greg,

To create the extra columns, you could just use:

t.Columns.Add
t.Columns.Add

but you might also first want to be sure you had a 3-column table
to start with:

If t.Columns.Count <> 3 Then MsgBox "Ey!" : Exit Sub

If the column widths matter and the default behavior when you
run those lines (which depends on the sizing parameters set in
the table) doesn't produce what you want, use:

t.Columns(4).Width = 36
or t.Columns(4).Width = InchesToPoints(0.5)

(Table dimensions, like so much else, are something VBA digests
only if supplied in points.)

The '99' is often seen in mid$ expressions. The mid$ function
extracts a string within a string by specifying the starting
and ending positions of the characters to be extracted. Often
you know the position you want to start at, but you just want
the extracted string to be the whole remainder of the string.
In that case, rather than waste code computing the position of
the end of the string (if it's even computable; it often isn't),
you can just use an improbably large number to specify it. The
function doesn't choke: mid$("argh",3,4) and mid$("argh",3,99)
both evaluate to "gh".

So if you had, say, the entire Gettysburg Address selected in a
document, this:

Dim s As String
s = Selection.Text
s = mid$(Selection.Text, 16, 9999)

would assign to 's' everything from "seven years ago" onward.

Of all the string functions, Mid$ can at first be the hardest to
wrap your brain around, but it's probably also the most useful.
Without it, a lot of parsing operations (e.g., wrestling unruly
mailing data into compliance) would be impossible.

 

[Greg Maxey]

Mark,

Copy all. Thanks.
I am going to see if I can figure out how to label the new column 4
"Initial" and the new column 5 "Day of Month."

--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[Greg Maxey]

Mark,

Thanks for the lessons.

Final result:
Sub ExtractAndList()
'Runs on a 3 column table. Extracts the first letter of column 1 and lists
a single _
'character initial in new column 4. Extracts value between "/" and "/" in
column 3 and lists _
'results in new column 5.
Dim t As Table, ro As Row
Set t = ActiveDocument.Tables(1)
t.Columns.Add
t.Columns(4).Width = 36
t.Columns.Add
t.Columns(5).Width = 36
t.Cell(1, 4).Range.InsertBefore "Initials"
t.Cell(1, 5).Range.InsertBefore "Day of Month"
For Each ro In t.Rows
If InStr(ro.Cells(3).Range.Text, "/") = 0 Then GoTo SkipThisRow
ro.Cells(4).Range.Text = Left(ro.Cells(1).Range.Text, 1)
With ro.Cells(3).Range
ro.Cells(5).Range.Text = Mid$(.Text, InStr(.Text, "/") + 1, _
InStr(Mid$(.Text, InStr(.Text, "/") + 1, 99), "/") - 1)
End With
SkipThisRow:
Next ro
End Sub


--
\\\\///////////
( @ @ )
----oo00---(_)---00oo----
Greg Maxey
A peer in "peer to peer" support
Rockledge, FL

 

[Mark Tangard]

Aw cmon, pretty it up a bit!

t.Cell(1, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
t.Cell(1, 5).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter

MT