word 2002: text replacement VBA not replacing 100%

[dgr7]

hello,
I have this VBA code that I want to run on a .csv comma-delimited file that I save from Excel. The VBA should replace every , occurance with a Line-Feed character. When I run it, it replaces every , correctly *except* for the last , occurance in the document, which it seems to replace with "".

Can anyone tell me why all , 's aren't being replaced and how to change this code to make it work on every , occurance in the .csv file instead of all but the last one?

thanks in advance,
david

before VBA code
---------
163206,107043317
165962,107093148
170621,106693773
-------

after VBA code after I've saved the document as a .txt then reopened the .txt
-------
163206
107043317
165962
107093148
170621106693773
--------

Application.DisplayAlerts = False
    
    Selection.WholeStory
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = ","
        .Replacement.Text = "Chr(10)"
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
    Application.DisplayAlerts = True

 

[Macropod]

Hi dgr7,

Presumably you don't really want to replace the commas with the string "Chr(10)", but with a line feed or a carriage return. In that case, you could use

.Replacement.Text = "^l"

or

.Replacement.Text = "^p"

Other than that, the code works fine for me with the data you posted. You could make it more efficient, by avoiding unnecessary selections, with a construct like:

Sub Test()
Application.DisplayAlerts = False
With ActiveDocument.Range
    .WholeStory
    With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = ","
        .Replacement.Text = "^l"
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
End With
Application.DisplayAlerts = True
End Sub

Cheers

 

[dgr7]

thank you , that worked great!