Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Replace one line in a text file (Excel97)

    Hi

    I'm hopeless at reading and writing to external files, and am struggling with what is probably a very simple problem.

    I want my macro to read the attached file ("defaults.txt"), find the first line that does NOT begin with "###, read the values in that line into some variables (the "fields" are comma-delimited, with all value surrounded by double-quotes. I've succeed with that part, as follows:
    <pre>Private Sub Defaults_GetFromFile()
    Dim MyDefaultsFileFullname$
    Dim MyStaffMemberName$, MyStaffMemberDirectDial, MyBranch$
    '------------------------------------------------------
    'Get the last used values from the file
    MyDefaultsFileFullname$ = MyFolder_Defaults & MyFilename_Defaults
    Close #1
    Open MyDefaultsFileFullname$ For Input As #1
    Do While Not EOF(1)
    Input #1, MyStaffMemberName$, MyStaffMemberDirectDial, MyBranch$
    MyStaffMemberName$ = Trim$(MyStaffMemberName$)
    MyStaffMemberDirectDial = Trim$(MyStaffMemberDirectDial)
    MyBranch$ = Trim$(MyBranch$)
    If Left$(MyStaffMemberName$, 1) = "#" Then
    'This line is just a comment
    'Loop through to the next line
    Else
    'This line contains the real data
    'Use it, then exit the procedure
    StaffMember_Name.Value = MyStaffMemberName$
    StaffMember_DirectDial.Value = MyStaffMemberDirectDial
    Branch.Value = MyBranch$
    GoTo Finish
    End If
    Loop
    '------------------------------------------------------
    Finish:
    Close #1
    Exit Sub
    '------------------------------------------------------
    ErrorProcessing:
    Call Tools.ErrorMessage_Display("ACC3.Defaults_GetFromF ile")
    End Sub
    </pre>


    Later on, I want change the line I read above, inserting new values into the (in this case) three fields. I can't for the life of me get that part to work. Here's one of my (failed) efforts:
    <pre>Private Sub Defaults_SaveInFile()
    Dim MyDefaultsFileFullname$, MyCounter As Integer, MyReplacementString$
    '------------------------------------------------------
    MyDefaultsFileFullname$ = MyFolder_Defaults & MyFilename_Defaults
    Close #1
    Open MyDefaultsFileFullname$ For Input As #1
    'Find the line to change
    Do While Not EOF(1)
    Input #1, MyStaffMemberName$, MyStaffMemberDirectDial, MyBranch$
    MyCounter = MyCounter + 1
    MyStaffMemberName$ = Trim$(MyStaffMemberName$)
    MyStaffMemberDirectDial = Trim$(MyStaffMemberDirectDial)
    MyBranch$ = Trim$(MyBranch$)
    If Left$(MyStaffMemberName$, 1) = "#" Then
    'This line is just a comment
    'Loop through to the next line
    Else
    'This line contains the real data
    'Replace it, then exit the procedure
    MyReplacementString$ = """" & MyStaffMemberName$ & """" _
    & ", """ & MyStaffMemberDirectDial & """" _
    & ", """ & MyBranch$ & """"
    Close #1
    Put #1, MyCounter, MyReplacementString$
    GoTo Finish
    End If
    Loop
    '------------------------------------------------------
    Finish:
    Close #1
    Exit Sub
    '------------------------------------------------------
    ErrorProcessing:
    Call Tools.ErrorMessage_Display("ACC3.Defaults_GetFromF ile")
    End Sub
    </pre>


    I've tried all sorts of variations (Write, Print, Random, dancing bears). Can someone put me on the right track. And, remember, I'm stupid.

    Thanks for your help
    Dale

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace one line in a text file (Excel97)

    Dale, I do this all the time with my INI file utilities.

    1) I read the entire file into a string variable. Open the file as input, grab the contents, close the file.

    2) I manipulate the string, locating relevant text with the INSTR function, using Left$ and Mid$ and Right$ to partition sections of the string.

    3) I make changes to the sub-string, as demanded

    4) I glue the Left, changed Mid and Right portions back into a single string

    5) I open the file for Output and rewrite the entire string.

    This approach satisfies any need for replacing contents of part of a file.

    If this makes sense to you, please let me know which step(s) are likely to cause you problems.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace one line in a text file (Excel97)

    I don't understand what you're trying to do - you seem to want to write the values back into the file without changing them, so what's the use?

    Anyhow, if you want to change a line, you should use two files: an input file and an output file.
    - Open the text file for input
    - Open a new file for output
    - Read the input file line by line
    - Write each line, modified if necessary, to the output file
    - When done, close both files, then delete the input file and rename the output file.

    Private Sub Defaults_SaveInFile()
    Dim MyDefaultsFileFullname As String
    Dim TempFile As String
    Dim MyStaffMemberName As String, MyStaffMemberDirectDial As String, MyBranch As String
    Dim intInput As Integer
    Dim intOutput As Integer
    '------------------------------------------------------
    MyDefaultsFileFullname = MyFolder_Defaults & MyFilename_Defaults
    intInput = FreeFile
    Open MyDefaultsFileFullname For Input As #intInput
    'Random name
    TempFile = MyFolder_Defaults & "blrgh1234.txt"
    intOutput = FreeFile
    Open TempFile For Output As #intOutput
    'Find the line to change
    Do While Not EOF(intInput)
    Input #intInput, MyStaffMemberName, MyStaffMemberDirectDial, MyBranch
    MyStaffMemberName = Trim(MyStaffMemberName)
    MyStaffMemberDirectDial = Trim(MyStaffMemberDirectDial)
    MyBranch = Trim(MyBranch)
    If Left(MyStaffMemberName, 1) = "#" Then
    'Pass unchanged
    Else
    'This line contains the real data - modify them
    ' ---? this part is not clear to me ?---
    End If
    'Write to output file
    Write #intOutput, MyStaffMemberName, MyStaffMemberDirectDial, MyBranch
    Loop
    Close #intInput
    Close #intOutput
    Kill MyDefaultsFileFullname
    Name TempFile As MyDefaultsFileFullname
    Exit Sub
    '------------------------------------------------------
    Finish:
    'Make sure files are closed
    Close #intInput
    Close #intOutput
    Exit Sub
    '------------------------------------------------------
    ErrorProcessing:
    Call Tools.ErrorMessage_Display("ACC3.Defaults_GetFromF ile")
    Resume Finish
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Replace one line in a text file (Excel97)

    This may or may not be simpler than other recommendations, you can also use FileSystemObject (FSO) TextStream object for this type of thing. Example:

    <code>Public Sub ReadAndWriteTextFile()</code>
    <code> On Error GoTo Err_Handler</code>
    <code> </code>
    <code> Dim fso As New FileSystemObject</code>
    <code> Dim txtOld As Scripting.TextStream</code>
    <code> Dim txtNew As Scripting.TextStream</code>
    <code> Dim strPath As String</code>
    <code> Dim strFile As String</code>
    <code> Dim strFileNew As String</code>
    <code> Dim strLine As String</code>
    <code> Dim strFields() As String</code>
    <code> Dim strDelimiter As String</code>
    <code> Dim n As Long</code>
    <code> Dim strMsg As String</code>
    <code> </code>
    <code> strPath = "C:Access"</code>
    <code> strFile = "Defaults.txt"</code>
    <code> strFileNew = " Copy Of " & strFile</code>
    <code> </code>
    <code> ' Make temp copy of file to write to:</code>
    <code> fso.CopyFile Source:=strPath & "" & strFile, _</code>
    <code> Destination:=strPath & "" & strFileNew</code>
    <code> </code>
    <code> Set txtOld = fso.OpenTextFile(strPath & "" & strFile, ForReading, False, TristateUseDefault)</code>
    <code> Set txtNew = fso.OpenTextFile(strPath & "" & strFileNew, ForWriting, True, TristateUseDefault)</code>
    <code> strDelimiter = ", "</code>
    <code> </code>
    <code> Do Until txtOld.AtEndOfStream</code>
    <code> strLine = txtOld.ReadLine</code>
    <code> If Mid$(strLine, 2, 3) = "###" Then</code>
    <code> ' do nothing</code>
    <code> Else</code>
    <code> ' Test only:</code>
    <code> Debug.Print "Current text: " & strLine</code>
    <code> ' Capture delimited text in array:</code>
    <code> strFields = Split(strLine, strDelimiter, -1, vbBinaryCompare)</code>
    <code> </code>
    <code> ' Update fields to new values (?):</code>
    <code> strFields(0) = Chr$(34) & "Chandler Bing" & Chr$(34) ' New Staff Member Name</code>
    <code> strFields(1) = Chr$(34) & "(999) 123-4567" & Chr$(34) ' New Direct Dial No</code>
    <code> strFields(2) = Chr$(34) & "Central Perk Branch" & Chr$(34) ' New Branch name</code>
    <code> </code>
    <code> ' Convert new values back to delimited text string:</code>
    <code> strLine = Join(strFields, strDelimiter)</code>
    <code> ' Test only:</code>
    <code> Debug.Print "Revised text: " & strLine</code>
    <code> End If</code>
    <code> ' Write to new file:</code>
    <code> If Not txtOld.AtEndOfStream Then</code>
    <code> txtNew.WriteLine strLine</code>
    <code> Else</code>
    <code> ' Use Write not WriteLine (no NewLine character):</code>
    <code> txtNew.Write (strLine)</code>
    <code> End If</code>
    <code> Loop</code>
    <code> </code>
    <code> ' Close both TextStreams:</code>
    <code> txtOld.Close</code>
    <code> txtNew.Close</code>
    <code> </code>
    <code> ' Copy new file over old file:</code>
    <code> fso.CopyFile Source:=strPath & "" & strFileNew, _</code>
    <code> Destination:=strPath & "" & strFile, _</code>
    <code> OverwriteFiles:=True</code>
    <code> ' Delete "temp" file:</code>
    <code> fso.DeleteFile strFileNew</code>
    <code> </code>
    <code>Exit_Sub:</code>
    <code> Set fso = Nothing</code>
    <code> Set txtOld = Nothing</code>
    <code> Set txtNew = Nothing</code>
    <code> Exit Sub</code>
    <code>Err_Handler:</code>
    <code> strMsg = "Error No " & Err.Number & ": " & Err.Description</code>
    <code> MsgBox strMsg, vbExclamation, "ERROR MESSAGE"</code>
    <code> Resume Exit_Sub</code>
    <code>End Sub</code>
    <code></code>
    ' Test results - only last line in text file changed:
    <code>'Current text: "Dale Lacey", "(09) 475-8750", "Wellington Branch"</code>
    <code>'Revised text: "Chandler Bing", "(999) 123-4567", "Central Perk Branch"</code>

    Note this requires reference to "Microsoft Scripting Runtime" library. Sample text file had comma & space as delimiter, if this not correct, modify strDelimiter variable (used with Split & Join functions to convert delimited text to array then back again before writing to new textfile). Note when writing to new file, if at EndOfStream, the Write (not WriteLine) method is used to write text because WriteLine automatically adds a NewLine character at end of text, which, it appears, is not desired in this case.

    HTH

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Replace one line in a text file (Excel97)

    Thank you, all. Even I can understand most of that. I'll use Hans's "two files" solution on this occasion, and keep the other appoaches in my back pocket for later use.

    Thanks for your help
    Dale

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •