Replace in table   

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.

date: Fri, 22 Aug 2008 19:27:04 +0100   author:   CS

RE: Replace in table   

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" wrote:

> 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.
>

date: Fri, 22 Aug 2008 13:18:00 -0700   author:   StevenM stevencraigmiller(at)comcast(dot)net

Re: Replace in table   

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 wrote:
> To: CS,
> 
> '
> ' Place cursor within table and run macro.
> '

date: Sat, 23 Aug 2008 11:46:38 +0100   author:   CS

Re: Replace in table   

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" wrote:

> 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

date: Sat, 23 Aug 2008 05:36:01 -0700   author:   StevenM stevencraigmiller(at)comcast(dot)net

Re: Replace in table   

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

date: Sat, 23 Aug 2008 19:54:20 +0100   author:   CS