Edit a text file with VBA

45 pts.
Tags:
Access 2007
Text manipulation
VB
VBA
Cheers, I have large text file that needs to be edited to run a job but I would like to automate the process.  How do I open a file and replace line 132 of (which is about mid of the file) that file? Thanks, Bob

Software/Hardware used:
Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could use a script like this. Actually, it does not “replace” the line, but reads the complete file and writes the modified text back. It could take some time (and resources) to process large files this way.

<pre>Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\your_file.txt", ForReading)

iLineNumber = 0
Do Until objFile.AtEndOfStream
strLine = objFile.Readline
iLineNumber = iLineNumber + 1
<b>If iLineNumber = 132</b> Then
strNewContents = strNewContents &<b> "Your new line"</b> & vbCrLf
else
strNewContents = strNewContents & strLine & vbCrLf
End If
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\your_file.txt", ForWriting)
objFile.Write strNewContents
objFile.Close</pre>

-CarlosDL
————————

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rjwilcox
    Carlos, Thanks for the code but I am unable to get the .AtEndOfStream to work. It does not recognize the code. I have referenced Microsoft Office 12.0 Object Library without success. Thanks Bob
    45 pointsBadges:
    report
  • carlosdl
    Sorry Bob, I tested it as a vb script. I think you will need to add a reference to The Microsoft Scripting Runtime Object Library Let me know if this solves the problem.
    68,780 pointsBadges:
    report
  • VBAzip
    How to add password to text file by VBA code?
    10 pointsBadges:
    report
  • MelanieYarbrough
    Hi VBAzip, If you start a new thread for your question, you're more likely to get a helpful response. Please include as many details as possible! Thanks! - Melanie
    6,345 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following