Replace in table

[CS]

All,

Completely new to Word VBA and seeking a bit of help.

I have a table which contains dates in the format dd.mm.yyyy which need
converting to dd/mm/yyyy

I have found some code which does the replacement, but, unfortunately it
replaces in the whole table rather than just the columns I need which
are 2, 4 and 6

Could someone point me in the right direction to just replace the . with
a / in the right columns ?

Many thanks in advance.

 

[StevenM]

To: CS,

'
' Place cursor within table and run macro.
'
Sub CorrectDatesInColumns246()
Dim oTable As Table
Dim col As Long
Dim row As Long
Dim s As String

If Selection.Information(wdWithInTable) = False Then
MsgBox "The cursor must be positioned in the table you want to
corrected."
Exit Sub
End If
Set oTable = Selection.Tables(1)
For col = 2 To 6 Step 2
For row = 1 To oTable.Columns(col).Cells.count
s = oTable.Columns(col).Cells(row).Range.Text
s = Left(s, Len(s) - 2)
s = Replace(s, ".", "/")
oTable.Columns(col).Cells(row).Range.Delete
oTable.Columns(col).Cells(row).Range.Text = s
Next row
Next col
End Sub

Steven Craig Miller

 

[CS]

Steven,

Many, many thanks for this its works a treat.

One quick question, is there a way to remove leading spaces from the
text entered. Now that I've sorted out the first step I've noticed a few
records with leading spaces.

Chris

snip snip

 

[StevenM]

To: CS,

'
' Trim Text in Tables
'
Sub TrimTextInTables()
Dim oTable As Table
Dim oCell As Cell
Dim sStr As String

For Each oTable In ActiveDocument.Tables
For Each oCell In oTable.Range.Cells
sStr = oCell.Range.Text
If Len(sStr) > 2 Then
sStr = Left(sStr, Len(sStr) - 2)
sStr = Replace(sStr, Chr(160), " ")
sStr = Trim$(sStr)
oCell.Range.Delete
oCell.Range.Text = sStr
End If
Next oCell
Next oTable
End Sub

Please note that the line: sStr = Replace(sStr, Chr(160), " ")

Replaces nonbreaking spaces with a normal space.

You may delete that line, if you wish.

The function Trim removes both leading and trailing spaces.

It trims text in every table.

If you prefer it to work only on one table, then:

'
' Trim Text in a Table
'
Sub TrimTextInATable()
Dim oTable As Table
Dim oCell As cell
Dim sStr As String

If Selection.Information(wdWithInTable) = False Then
MsgBox "The cursor must be positioned in the table."
Exit Sub
End If
Set oTable = Selection.Tables(1)

For Each oCell In oTable.Range.Cells
sStr = oCell.Range.Text
If Len(sStr) > 2 Then
sStr = Left(sStr, Len(sStr) - 2)
sStr = Replace(sStr, Chr(160), " ")
sStr = Trim$(sStr)
oCell.Range.Delete
oCell.Range.Text = sStr
End If
Next oCell
End Sub


Steven Craig Miller

 

[CS]

Steven

Many thanks once again.

I have now managed to put together a routine inside Access which tidies
the data and then imports the table into Access via a temp csv file.

Many, many thanks once again.

Chris

To: CS,

'
' Trim Text in Tables

snip snip snip