reading and replacing just the first line of a txt or csv file XL2



We have a report that is generated from "System A" and the column headers are
in mixed case. We are uploading the file into "System B" which requires all
column headers to be in uppercase.

The end result is that I just need to read the first line, change it to
uppercase, and paste it back into the file over the original first line. My
code (so far, and not working) is below, and here are some general questions
to get me on track:

1. If I recall correctly, when reading text files there is some way to
specify whether the file is being opened for reading, writing, or both (I'd
need both). The OpenTextFile help shows reading OR appending, but not both.
Do I need to close the file and re-open it in the other mode?

2. the OpenTextFile paramater description "appending" implies that any new
text will be added to the end of the file, rather than replacing just the
first line. Is there a different approach I should use to overwrite the first
line, instead of append? like writeline(1)...?

3. Is this the right approach in the first place, or should I just read the
whole dang file, change the first line, and then just write it all to a new
file? That just seems wasteful...

Sub ChangeHeaders()

Dim oFSO As New FileSystemObject
Dim oFS

Dim FileName As Variant
Dim Sep As String
FileName = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*.txt")
If FileName = False Then
Exit Sub
End If

Set oFS = oFSO.OpenTextFile(FileName)

sText = oFS.ReadLine
sText = UCase(sText)
oFS.WriteLine (sText) '<-- errors, probably because file was open for reading

End Sub

Many thanks,

Tim Williams

Yes, just create a new file. At least that way if anything goes wrong
you can just compare the two versions.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question