replace, instr etc. in text file

[muybn]

I'm trying to do a VB command that will replace all instances of "," with a
tab in a text file. My code is:

Sub test()
Dim strScrap As String, strRepl As String, intCt As Integer, arrText As
Variant

strRepl = Chr(34) & Chr(44) & Chr(34)
Open "C:\directory\file" For Input As #1
Do While InStr(strScrap, strRepl) > 0
Line Input #1, strScrap
arrText = Replace(strScrap, strRepl, Chr(9))
Loop
Close #1
End Sub

When I execute it, however, I find instr = 0 but the file is full of this
text. What am I missing?

 

[Doug Robbins - Word MVP]

Why don't you just record the use of the Edit>Replace menu item with a comma
in the Find what control and ^t in the Replace with control.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[muybn]

I normally do and I would, but with the huge amount of records I'm affecting
in this particular macro, Klaus advised me to use this method for better
speed and memory considerations.

 

[Russ]

Bryan,
Is this closer to what you want? I didn't test it. But looking at it in a
program flow type of way, it looks workable. It loads arrText with each
amended line and unaltered line of the file and you'll need to write them
back out with another subroutine.

Sub test()
Dim strScrap As String, strRepl As String, intCt As Integer
Dim arrText() As String

strRepl = Chr(34) & Chr(44) & Chr(34)
Open "C:\directory\file" For Input As #1
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, strScrap
ReDim Preserve arrText(1 To UBound(arrText) + 1)
If InStr(strScrap, strRepl) > 0 then
arrText(UBound(arrText)) = Replace(strScrap, strRepl, Chr(9))
Else
arrText(UBound(arrText)) = strScrap
End If
Loop
Close #1
End Sub

....
For intCt = 1 to UBound(arrText)
'write each line back out
Next intCt

 

[mary fran]

Our question is similar, so perhaps you can help us as well.

We are trying to find comma followed by number and replace the comma with a
tab, followed by the same number. We do not want to replace all commas with
a tab, just those commas followed by a number.

Find , ^#
Replace ^t^&

This does not work because it adds the tab but leaves the comma. What do we
need to do differently?

 

[Jay Freedman]

Use a wildcard search
(http://www.gmayor.com/replace_using_wildcards.htm) with these
expressions:

Find , ([0-9]{1,})
Replace ^t\1

The Find expression (which is not valid if wildcard searching is
turned off) uses the parentheses to mark the number part of the
expression, and the \1 in the Replace expression refers to that part.

It appears that your first attempt included a space between the comma
and the number, so I included it above. If that's not correct, remove
the space.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

On Sat, 14 Jul 2007 09:12:04 -0700, mary fran <mary