Prev: make message box appear when cell is clicked?
Next: Resize a Table using VBA
From: ker_01 on 7 Apr 2010 16:49
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

From: Tim Williams on 7 Apr 2010 18:44
Yes, just create a new file. At least that way if anything goes wrong
you can just compare the two versions.

Tim

On Apr 7, 1:49 pm, ker_01 <ke...(a)discussions.microsoft.com> wrote:
> 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

 | 
Pages: 1
Prev: make message box appear when cell is clicked?
Next: Resize a Table using VBA