Edit a text file with 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
Software/Hardware used:
Access 2007
ASKED:
January 28, 2010 3:18 AM
UPDATED:
August 26, 2010 7:59 PM
Answer Wiki:
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
------------------------ 
To see all answers submitted to the Answer Wiki: View Answer History.
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
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.
How to add password to text file by VBA code?
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