Text File - Find and Replace Specific Line

GettingThere

New Member
Joined
Mar 10, 2009
Messages
19
Hi guys,

I have an Excel userform that takes user data and writes it to a txt file. Certain function it has also reads the data by the user specifying a line number and the macro reading until it gets to that line and then displaying it as required.

However once it has reached the specific line is there any way I can:

1. Read the line in to a string
2. Find and replace what I need to
-----I can do the above two points---
3. Delete the original line, and
4. Write the new string back in it's place? - So not at the end but at the same line number as the original source.

Any suggestions would be much appreciated!! =D

Thanks,

A.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi,

When you are reading and replacing, where are you reading from, I.E. are you loading it from the txt file?, or before you are saving it?

Regards

ColinKJ<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 
Upvote 0
Hi,

From the text file.

When I writing to the text file - it's just writing from text entered into the userform textboxes, e.g.

--------------------
<CODE>
Dim tText As String
tText = txt1.Value & ";" & txt2.Value & ";" & txt3.Value & ";" & _
txt4.Value & ";" & txt5.Value

Open FileName For Append As #fNum
Write #fNum, tText
Close #fNum
</CODE>
<CODE>
--------------------

to look up the user inputs a reference (line number):

--------------------
</CODE>
Dim FileName As String, fNum As Integer
FileName = "G:\blahblah\Arthur.txt"
fNum = FreeFile

Dim sText As String
Dim delimText() As String
Dim lNum As Integer
On Error Resume Next
lNum = InputBox("Input your line number")
On Error GoTo 0
If lNum > 0 Then
lbl1.Caption = lNum
Else
lbl1.Caption = ""
End If

Dim i As Integer
i = 0
Open FileName For Input As #fNum
While Not EOF(fNum)
Line Input #fNum, sText
i = i + 1
If i = lNum Then
delimText = Split(sText, ";")
txt1.Value = delimText(0)
txt2.Value = delimText(1)
txt3.Value = delimText(2)
txt4.Value = delimText(3)
txt5.Value = delimText(4)
GoTo john
End If
Wend
If i < lNum Then
MsgBox ("Entry not found"), vbOKOnly & vbCritical, "Not Found"
UserForm_Initialize
End If
john:
Close #fNum
Exit Sub
--------------------
</CODE>

What I would like is something similar to the second macro above but once it has been read into the variable 'sText' that I could replace text within this string and write it back over the line it was originally read from - It's the writing it back to the same place bit that I cannot figure out...

Does that make sense?? =D

I basically just want a find and replace within a text file but without (a) having to go into the file itself (which are hidden from users...) and (b) having to read the whole file into a new file and saving it over the original (which was the only way I could think of...)


Thanks,

A =D
 
Upvote 0
Looking at your code it appears you are continually building up the size of your text wile by the use od Append. If therefore it is a large file I can understand why you don't want to have to load the whole file, make the change you want and save it again..

You maywant to take a look at the Visual Basic Help file

Writing Data to Files


<OBJECT id=hhobj_1 style="LEFT: 10px; WIDTH: 42px; TOP: 39px; HEIGHT: 13px" type=application/x-oleobject height=13 width=42 classid=clsid:adb880a6-d8ff-11cf-9377-00aa003b7a11>





</OBJECT> <OBJECT id=hhobj_2 style="LEFT: 66px; WIDTH: 43px; TOP: 39px; HEIGHT: 13px" type=application/x-oleobject height=13 width=43 classid=clsid:adb880a6-d8ff-11cf-9377-00aa003b7a11>





</OBJECT></P>
When working with large amounts of data, it is often convenient to write data to or read data from a file. The Open statement lets you create and access files directly. Open provides three types of file access:
  • <LI class=LB1>Sequential access (Input, Output, and Append modes) is used for writing text files, such as error logs and reports.

    <LI class=LB1>Random access (Random mode) is used to read and write data to a file without closing it. Random access files keep data in records, which makes it easy to locate information quickly.
  • Binary access (Binary mode) is used to read or write to any byte position in a file, such as storing or displaying a bitmap image. Note The Open statement should not be used to open an application's own file types. For example, don't use Open to open a Word document, a Microsoft Excel spreadsheet, or a Microsoft Access database. Doing so will cause loss of file integrity and file corruption.
The following table shows the statements typically used when writing data to and reading data from files.
<TABLE cellSpacing=4 cols=3 cellPadding=4><TBODY><TR vAlign=top><TH width="21%">Access Type</TH><TH width="24%">Writing Data</TH><TH width="55%">Reading Data</TH></TR><TR vAlign=top><TD class=T width="21%">Sequential</TD><TD class=T width="24%">Print #, Write #</TD><TD class=T width="55%">Input #</TD></TR><TR vAlign=top><TD class=T width="21%">Random</TD><TD class=T width="24%">Put</TD><TD class=T width="55%">Get</TD></TR><TR vAlign=top><TD class=T width="21%">Binary</TD><TD class=T width="24%">Put</TD><TD class=T width="55%">Get</TD></TR></TBODY></TABLE>

The Random Access looks as if it may suit your application better.

I've not used it myself, so you may need to read up on it, or make another post.

ColinKJ
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top