replace, instr etc. in text file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

muybn - 16 Jun 2007 23:07 GMT

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?

Signature

Bryan

Reply to this Message

Doug Robbins - Word MVP - 16 Jun 2007 23:27 GMT

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.

Signature

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

> I'm trying to do a VB command that will replace all instances of "," with
> a
[quoted text clipped - 15 lines]
> When I execute it, however, I find instr = 0 but the file is full of this
> text. What am I missing?

Reply to this Message

muybn - 16 Jun 2007 23:59 GMT

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.

Signature

Bryan

> 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.
[quoted text clipped - 18 lines]
> > When I execute it, however, I find instr = 0 but the file is full of this
> > text. What am I missing?

Reply to this Message

Russ - 17 Jun 2007 01:37 GMT

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

> 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
[quoted text clipped - 22 lines]
>>> When I execute it, however, I find instr = 0 but the file is full of this
>>> text. What am I missing?

Signature

Russ

drsmN0SPAMikleAThotmailD0Tcom.INVALID

Reply to this Message

mary fran - 14 Jul 2007 17:12 GMT

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?

Reply to this Message

Jay Freedman - 14 Jul 2007 20:04 GMT

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 FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

>Our question is similar, so perhaps you can help us as well.
>
[quoted text clipped - 30 lines]
>> > When I execute it, however, I find instr = 0 but the file is full of this
>> > text. What am I missing?

Reply to this Message