Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Modules / DAO / VBA / September 2005

Tip: Looking for answers? Try searching our database.

replacing text in textfile

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jesper - 01 Sep 2005 17:05 GMT
I have a text file that's 20 lines long.
I specifically need to replace the word "no" to "yes" on line 10 of the
file.
Line 10 reads: "Locked=No" and needs to be "Locked=Yes"

Is that possible? Can it be done with VBA I/O or do I need the Scripting
Runtime?
Thanks for any input.

Jesper, Denmark
Dirk Goldgar - 01 Sep 2005 17:34 GMT
> I have a text file that's 20 lines long.
> I specifically need to replace the word "no" to "yes" on line 10 of
[quoted text clipped - 6 lines]
>
> Jesper, Denmark

You can do it with simple VBA I/O.  Quick, untested "air code" might
look like this:

   Dim intFile As Integer
   Dim strFileText As String
   Dim astrLines() As String

   Const conFilePath As String = "C:\Temp\SomeFile.txt"

   intFile = FreeFile

   ' Since it's a small file, we'll read it all at a gulp.
   Open conFilePath For Input As #intFile
   strFileText = Input(LOF(intFile), intFile)
   Close #intFile

   ' Split the file text into lines.
   astrLines = Split(strFileText, vbCrLf)

   ' Replace "Locked=Yes" on the tenth line.
   astrLines(9) = Replace(astrLines(9), "Locked=Yes", "Locked=No")

   ' Join the lines back together with carriage return+line feed
   ' characters between them.
   strFileText = Join(astrLines, vbCrLf)

   ' Write the text back out.
   ' NOTE:  I'm writing this to the same file.
   ' You may not want to do that.
   Open conFilePath For Output As #intFile
   Print #intFile, strFileText;
   Close #intFile

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Tim Ferguson - 01 Sep 2005 18:08 GMT
> I have a text file that's 20 lines long.
> I specifically need to replace the word "no" to "yes" on line 10 of
[quoted text clipped - 3 lines]
> Is that possible? Can it be done with VBA I/O or do I need the
> Scripting Runtime?

 
 open "myoldfile.txt" for input as #InFile
 open "mynewfile.txt" for output as #outfile

 for j = 1 to 20
   input line #infile, strTempLine
   
   if j = 10 then
     strTempLine = Replace(strTempLine, "Locked=No", "Locked=Yes")
   end if

   print #outfile, strTempline
 next j

 close #outfile
 close #infile

 Kill "myoldfile.txt"
 ' can't remember the syntax for this
 Rename "myNewFile.txt", "myoldfile.txt"

Isn't it easier to use TextPad or something?

Hope that helps

Tim F

   
Jesper - 01 Sep 2005 20:11 GMT
>  open "myoldfile.txt" for input as #InFile
>  open "mynewfile.txt" for output as #outfile
[quoted text clipped - 15 lines]
>  ' can't remember the syntax for this
>  Rename "myNewFile.txt", "myoldfile.txt"

Great, thanks very much to both of you.

Jesper, Denmark
Douglas J. Steele - 01 Sep 2005 23:07 GMT
<picky>
Line Input, not Input Line
</picky>

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>> I have a text file that's 20 lines long.
>> I specifically need to replace the word "no" to "yes" on line 10 of
[quoted text clipped - 29 lines]
>
> Tim F
Jesper - 02 Sep 2005 11:52 GMT
> <picky>
> Line Input, not Input Line
> </picky>

I actually had some trouble with so thanks.

Jesper F, Denmark
Tim Ferguson - 02 Sep 2005 17:34 GMT
> <picky>
> Line Input, not Input Line
> </picky>

<moment class="senior">
Thank you Matron -- here come my tablets.
</moment>

All the best

Tim F
Jack - 13 Sep 2005 13:37 GMT
Tim,
I am looking for a very similar procedure. What I am wanting to do is use
this procedure to replace text in a html file. I created a 'temp' txt file
that has all of the sections (3) cthat need to be replaced with
##replace1##,replace2##,##replace3##. I am hoping that I can have the replace
strings stored in a table. The ##replace## are throughout the text file - no
particular line. I need to do a replace all with the ##replace## string. I am
then needing to have the save as name stored in table. I would like to push a
command button on form and have access create 300+ html files on the fly. I
believe I can adapt paqrt of your code, however I am confused as to how set
it to replace anywhere it finds ##replace?## in text file, much less how to
instruct it to do same thing for next ##replace2## etc. Any help would be
greatly appreciated.
Thanks,
Jack

> > I have a text file that's 20 lines long.
> > I specifically need to replace the word "no" to "yes" on line 10 of
[quoted text clipped - 32 lines]
>
>    
Tim Ferguson - 13 Sep 2005 20:36 GMT
> I am looking for a very similar procedure.

I don't think it is very similar: the OP, as I remember it, had incredibly
tight requirements that made it easy to offer a simple, but hopelessly
importable, solution.

My first reaction to any kind of generic text manipulation, especially when
it comes to html tags and the like, is to get the PERL book out. If you
really want to use VB and its cousins, the built in Scripting.Regex is
nearly as flexible, but be prepared for a steep learning curve.

If you have really fixed targets, you can use Instr() and Replace() to find
them. The other problem with html type text is that you cannot expect to
find nice line-endings in appropriate places, and you have to work very
hard to make sure you don't miss having more than one target in the same
line.

Best of luck

Tim F
Jack - 14 Sep 2005 14:51 GMT
Tim,
Thank you for the reply. These are TRULY fixed targets. Generally the
##replace1## field within the txt file needs to replaced with one - three
words MAX. If this was a onesy twosey (did i spell that right:) )I wouldnt
have a problem. But 100+ pages need to be updated with every file having
different data. I created a template, went through and made the targets
##replace1##, ##replace2## and ##replace3##.  I then saved this as a text
file.

During my endless attempts to figure out how to do this. I have taken the
following steps. I just need assistance putting it together.

I created a table named tbl_replace - 3 text fields named replace1,2,etc I
added a fourth field as to what I will need the output filename to be.
I created a form with a command button and inserted the following code.

Sub ReplaceFile(Tempfile As String, ReplaceFile As String, Offset As Integer)
On Error GoTo Err_ReplaceFile

Dim db As DAO.Database
Dim Directory As String
Dim MyString As String
Dim NewString As String
Dim MyChar As String
Dim TagFound As Boolean
Dim i As Integer

Set db = CurrentDb
Directory = Mid(db.Name, 1, Len(db.Name) - Len(Dir(db.Name)))

Open Directory & "\" & Tempfile For Input As #1 !This works fine
Open Directory & "\" & ReplaceFile For Output As #2! I havent figured out
how to get access to loop through all of my records. Maybe a d lookup?
TagFound = False

'loop through text file
Do While Not EOF(1)
   NewString = ""
   'read one line
   Line Input #1, MyString
   Do Until instr(MyString) = 0
       'walk down the line
      !This is part I am confused about
something like:

replace(mystring,##replace1##,[field from tbl_replace])
replace(mystring,##replace2##,[field from tbl_replace])
replace(mystring,##replace3##,[field from tbl_replace])
          Loop
   'Output the line Record
   Print #2, NewString ! is this best way to do this?
Loop

MsgBox "Done"

Exit_ReplaceFile:
   Close #2
   Close #1
   Set db = Nothing
   Exit Sub

Err_ReplaceFile:
   If Err.Number = 62 Then
       Resume Exit_ReplaceFile
   
   Else
      MsgBox Err.Number
       MsgBox Err.Description
       Resume Exit_ReplaceFile
   End If

End Sub

Any assistance would be greatly appreciated.
Thanks again,
Jack

> > I am looking for a very similar procedure.
>
[quoted text clipped - 16 lines]
>
> Tim F
Jack - 14 Sep 2005 16:13 GMT
Ok... I believe I am getting somewhere. I scoured the net and found the
following code. I created a sample form with 3 text boxes (Text1,Text2,Text3)
I created 2 buttons(replace text and replacefile)
Replacetext give me and error 2185 focus error
ReplaceFile does indeed creat the replacement file - but nothing is in it.
Can someone Please take a look at the code and give me a clue as to what may
be going on. I am really trying to learn and am not asking to have code
rewritten.

Thanks, Again
Jack
Code Follows:

This is a simple text replacement example. It will also work on Binary files
since I included the description on how to make it reusable from a form with
3 text boxes and how to read in a full binary or ascii file as a single
string. Usage:

Private Sub cmdReplaceText_Click()
   'replaces all occurances of Text2 in Text1 with Text3
   Call ReplaceTextInTextBox(Text1, Text2, Text3)
End Sub
Private Sub cmdReplaceFile_Click()
   'replaces all occurances of "hello" with "goodbye" in c:\test.txt, and
saves the new file as c:\test_replace.txt
   Call ReplaceInFile("c:\test.txt", "c:\test_replace.txt",
"hello","goodbye")
End Sub

Place the code below in a module

Public Function ReplaceTextInTextBox(MyTextBox As Object,_       TextOld As
Object, TextNew As Object)
'The object reference is made form the form
'you use to call this module.
'Assumed you have a text1, text2 and text3 text box on your form
'where 1 is used for the text you wish to replace text in
'2 is the one containing text you are searching for
'3 is the new text you wish to see entered in its place.
'The replacement function being called

 Call ReplaceText(MyTextBox.Text, TextOld.Text, TextNew.Text)

'Replace textview and refresh your form

   MyTextBox.Text = ReturnValue
   MyTextBox.Refresh

End Function

Public Function ReplaceInFile(InputFile As String, _
   OutputFile As String, OldT As String, NewT As String) As String
   Dim Fnum As Integer ' get a FreeFile number
   Dim FileLength As Long 'Just in case its really big
   Dim TheString As String

'I open the file as binary to avoid some
'complications with reading in a full file.
'This just so I can skip some trouble shooting with
'some special characters in certain exe files.
'It might be that character you wish to replace.

   Fnum = FreeFile
   Open InputFile For Binary As #Fnum ' Open file.
   FileLength = LOF(Fnum) ' Get length of file.

    TheString = Input(FileLength, #Fnum)

   Close #Fnum ' Close file.

'OldT = Whatever text you send form a form or function to search for
'NewT = Whatever text you wish to replace OldT with

'Call the function to replace text

  Call ReplaceText(TheString, OldT, NewT)

'Now print the result to a new file
'so you dont overwrite your original

   Fnum = FreeFile
   Open OutputFile For Output As #Fnum ' Open file.
   Print #Fnum, ReturnValue ' the ReturnValue from the replacement
   Close #Fnum ' Close file.

End Function

Public Function ReplaceText(CleanThis As String, _
   OldText As String, _
   NewText As String)

'To get the len of the string
   Dim StrLn As Long

'To split the string
   Dim PartA As String, PartB As String

'If search string is found, get its start position
   Dim FoundP As Long
   Dim OldLn As Long
   On Error GoTo ErrHandle

'Set a value for the len of the old text to be replaced
   OldLn = Len(OldText)
   StrLn = Len(CleanThis)

'Loop through the string until all occurences are eliminated

   Do While InStr(1, CleanThis, OldText) <> 0
       FoundP = InStr(1, CleanThis, OldText)

'Get PartA of the string (before found occurance)
       PartA = Left(CleanThis, FoundP - 1)

'Get PartB of the string (after found occurance)
       PartB = Right(CleanThis, StrLn - FoundP - OldLn + 1)

'*NOTE ON THE ADDITION AND SUBTRACTION**
'******************************************************
'+ 1 to avoid a skip in adding found len and old len
'The previous line could also be written
'    
'PartB = Right(CleanThis, StrLn - (FoundP + OldLn - 1))
'******************************************************
'REBUILD THE STRING BEFORE NEXT LOOP
'This adds a space before and after NewText
'and trims out unneccessary spaces too!
       
       CleanThis = Trim(PartA) & " " & _
       Trim(NewText) & " " & _
       Trim(PartB)
       'GET NEW LEN BEFORE LOOPING
       StrLn = Len(CleanThis)
   Loop

   'Set result of the function
   ReplaceInString = CleanThis

'Avoid error handling
   Exit Function
'Add your own error code hereafter
   ErrHandle:
   Select Case Err.Number
       Case Err.Number
       MsgBox "Your function executed with an error " & Err.Number _        
   & vbCrLf & vbCrLf & Err.Description, vbExclamation, _            "Error "
& Err.Number
       Err.Clear
   End Select
Resume Next
End Function

> Tim,
> Thank you for the reply. These are TRULY fixed targets. Generally the
[quoted text clipped - 93 lines]
> >
> > Tim F
Tim Ferguson - 14 Sep 2005 18:18 GMT
> Please take a look at the code and give me a clue as
> to what may be going on.

See below: I'm not very impressed with the standard of coding going on
here...

> I am really trying to learn and am not asking
> to have code rewritten.

I guess it probably needs it...


>   Call ReplaceText(MyTextBox.Text, TextOld.Text, TextNew.Text)

This is garbage and never, ever, could have worked. The text property is
only available when a control has the focus, and three controls cannot
have the focus at the same time. In addition, there is no check that the
passed objects are text boxes (or listboxes, or combos..?)

The .Value property would have worked here.

> Public Function ReplaceText(CleanThis As String, _
>     OldText As String, _
>     NewText As String)

It's not strictly illegal, but it's really shabby to declare a function
without a return: this should be a Sub not a Function.

>     Do While InStr(1, CleanThis, OldText) <> 0

Unfortunately, this is going to fall apart badly if the target text is a
substring of the inserted text. Try
 

 ReplaceText(ExampleText, "open", "unopened")

and wait for a very long time...

> 'REBUILD THE STRING BEFORE NEXT LOOP
> 'This adds a space before and after NewText
[quoted text clipped - 3 lines]
>         Trim(NewText) & " " & _
>         Trim(PartB)

This is pretty wanton inserting and removing white space, which may or
may not be appropriate depending on the user's needs...

In my view, you probably need to start again, this time with a properly
working algorithm.  If I had to do it, I'd use a tool other than VBA; but
I do recognise that I don't really understand what it is you are trying
to do.

Best wishes

Tim F
Jack - 14 Sep 2005 18:44 GMT
Thank you again Tim for they reply. Maybe I should start from stratch as to
what I am looking for.

I have a .txt file with 3 variables that are located throughout the file
(##replace1##,##replace2##,etc) These are FIXED points.
I am wanting to have the table I have with 4 fields (one for each replace#
and one for what the output filename should be)
I am wanting to have a command button on form that when clicked will go
through text file and replace ALL instances of ##replace1## with
[replace1.tbl_replace] and do the same thing for ##replace2## and ##replace3##
After its complete I want it saved as [saveitas_tbl_replace]
Two things are important 1) It needs to loop through ALL the records I
believe I would use a dcount and dlookup to do that. A new file would be
created for EACH record. 2) the text that is replaced does not include any
additional whitespace (ie i want ##replace1## apples -> i want [green] apples
OR I want [to buy more] apples.)

In meantime I will look at your suggestions. Again thanks for your help.
Jack

> > Please take a look at the code and give me a clue as
> > to what may be going on.
[quoted text clipped - 53 lines]
>
> Tim F
Dirk Goldgar - 14 Sep 2005 19:02 GMT
> It's not strictly illegal, but it's really shabby to declare a
> function without a return: this should be a Sub not a Function.

I know what you're getting at, Tim, and I would agree except that Access
has a particular feature that makes declaring "conceptual Subs" as
Functions very attractive in some cases.  Specifically, you can call a
function directly from the event property of a form, report, or control,
using the syntax

   =FunctionName(arguments ...)

Any return value from the function is discarded.

This can come in very handy, both for creating lightweight, module-less
forms that nevertheless incorporate programmed behaviors, and for easily
sharing a behavior among many controls on a form without creating an
event procedure for each control.  You can't do the same thing with a
Sub.

That's not to say that this consideration applies in the OP's specific
case.  I'm just debating your blanket statement.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk Goldgar - 14 Sep 2005 19:19 GMT
> Ok... I believe I am getting somewhere. I scoured the net and found
> the following code. I created a sample form with 3 text boxes
[quoted text clipped - 4 lines]
> as to what may be going on. I am really trying to learn and am not
> asking to have code rewritten.
[...]
> Public Function ReplaceText(CleanThis As String, _
>     OldText As String, _
>     NewText As String)

If you're using Access 2000 or later, there's not much point to this
function.  The built-in Replace function that is part of the version of
VBA that ships with Access 2000 will do the job better.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

jussij@zeusedit.com - 14 Sep 2005 00:51 GMT
I would suggest using a tool like 'sed' to do this:

   http://www.student.northpark.edu/pemente/sed/

Jussi Jumppanen
Author: Zeus for Windows
http://www.zeusedit.com/lookmain.html
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.