K
ker_01
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,
Keith
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,
Keith