Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: replacing value in a flat file copied from excel

    Hi,

    Excel 2k

    I have written a program in excel that saves information from various cells:

    John 7/24/2004 14 3 1 0 00:16:42 00:59:28 05:00:32

    to a flat file called UserStat.log on my C:\Logcall\ directory
    On any given day, there maybe more that one line in that flat file:

    John 7/24/2004 14 3 1 0 00:16:42 00:59:28 05:00:32
    patrick 7/24/2004 14 3 1 0 00:16:42 00:59:28 05:00:32
    gilles 7/24/2004 14 3 1 0 00:16:42 00:59:28 05:00:32

    I want to be able to update any information for a particular line when the user saves
    his/her work back to the userstats.log file without deleting and any of the
    line either bellow or above the one in question. Say, I want to update the value '14' for 'Patrick', which is
    line 2, column 3.

    What I have written is too confusing and does not accomplish the task. I wanted to loop through the flat
    file and look for a paricular name. once the name is found, then update any of that information on that line only. I am able to
    loop through the flat file and find the name, but i don't know how to upate the information or delete the entire line and replace it with the newly updated info from the user when the work is saved.
    Can you help me?

    Here is the code I wrote:

    To save the info on the flat file

    Sub WriteUserStats()

    Dim FileName As String
    Dim strLine As String
    Dim intFn As Integer
    Dim strOutFile As String

    intFn = FreeFile
    strOutFile = "C:\LOGCALL\UserStat.log"

    Open strOutFile For Append As #intFn

    strLine = Range("C1").Value & " " & Date
    strLine = strLine & " " & Range("C4").Value & " " _
    & Range("d4").Value & " " _
    & Range("e4").Value & " " _
    & Range("f4").Value & " " _
    & Range("g4").Value & " " _
    & Format(Range("h4").Value, "HH:MMS") & " " _
    & Format(Range("i4").Value, "HH:MMS") & " " _
    & Format(Range("J4").Value, "HH:MMS")
    Print #intFn, strLine


    Close #intFn

    End Sub

    Then to find the name to update:

    Private Sub FindNameToUpdate()

    Dim c As Long
    Dim hFile As Long
    Dim strLine As String
    Dim FileName As String
    Dim TextLIne As Variant
    Dim sTableDetails As String
    Dim RepName As String

    Close #hFile
    hFile = FreeFile
    FileName = "C:\LOGCALL\UserStat.log"
    Open FileName For Input As #hFile
    Close #hFile

    Open FileName For Input As #hFile
    Line Input #1, TextLIne
    Do While Not EOF(1)
    Line Input #1, TextLIne

    Dim sp1, strlen As Integer
    Dim one, two As String
    sp1 = InStr(1, TextLIne, " ")
    one = Left(TextLIne, sp1 - 1)
    If one = "PATRICK" Then
    one = "ALEXANDER"
    strLine = one
    End If
    Loop

    Close #hFile
    WriteUserStats (strLine)
    End Sub

    Then to update the values for a particular line????

    Function WriteUserStats(myvar As String)

    Dim FileName As String
    Dim strLine As String
    Dim intFn As Integer
    Dim strOutFile As String
    Dim TextLIne As Variant

    intFn = FreeFile 'grab next available DOS file handle
    strOutFile = "C:\LOGCALL\UserStat.log"

    Open strOutFile For Append As #intFn
    'Print #intFn, myvar
    MsgBox myvar

    '************************************************* *********
    ' this part was to replace the value that matches the name
    'but that does not work.
    '************************************************* *********
    Do While Not EOF(1)
    Dim sp1 As Integer
    Dim one As String
    sp1 = InStr(1, myvar, " ")
    one = Left(myvar, sp1 - 1)
    If one = myvar Then
    strLine = myvar
    Print #intFn, strLine
    End If
    Loop

    '************************************************* *********

    Close #intFn

    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Replacing values in a file.

    Hi

    Using the specific data given then the following procedure and parser will replace the data if ‘patrick’ is the name.

    It simple opens the UserStat.txt file and another ‘New’ file and writes the data from one to the other, with modifications the parsed data if the correct conditions exist.

    A backup of the old file is made, the old file deleted and the new file renamed as the old file.

    If no mods are made then just the new file is deleted.

    Code:
    Option Explicit
    Dim Data(8) As Variant
    
    Sub ReadWrite()
        Dim ReadNo As Integer
        Dim WriteNo As Integer
        Dim Text As String
        
        Dim Modified As Boolean
        Dim i As Integer
        Const cPath As String = "C:\dbForums\"
    
        Modified = False
        
        ReadNo = FreeFile
        WriteNo = ReadNo + 1
        
        Open cPath & "UserStat.txt" For Input Access Read As #ReadNo
        Open cPath & "New.txt" For Output Access Write As #WriteNo
    
        Do Until EOF(ReadNo)
            Input #ReadNo, Text
            
            ParseLine Text, Data()
            If UCase(Data(0)) = "PATRICK" Then
                Data(0) = "Alexander"
                
                'ASSIGN VALUES FROM SPREADSHEET HEAR, MODIFED OR NOT !?
                'Data(1)= Cells(??)
                'Data(2) = Cells(??)
                '
                '
                'Data(8)=cells(??)
                
                Modified = True
            End If
            Text = Data(0)
            For i = 1 To UBound(Data)
                 Text = Text & " " & Data(i)
            Next i
    
            Write #WriteNo, Text
        Loop
        
        Close #ReadNo
        Close #WriteNo
    
        If Modified Then
            If Dir(cPath & "\UserStat.bak") <> "" Then Name cPath & "\UserStat.bak" As cPath & "UserStat.tmp"
            Name cPath & "UserStat.txt" As cPath & "UserStat.bak"
            Name cPath & "New.txt" As cPath & "UserStat.txt"
            If Dir(cPath & "\UserStat.tmp") <> "" Then Kill cPath & "UserStat.tmp"
        Else
            Kill cPath & "New.txt"
        End If
        
    End Sub
    
    Sub ParseLine(ByVal Text As String, ByRef Data() As Variant)
        Dim Pos1 As Integer
        Dim Pos2 As Integer
        Dim i As Integer
        
        Pos1 = InStr(Text, " ")
        If Pos1 = 0 Then Exit Sub
        
        Data(0) = Left(Text, Pos1 - 1)
        i = 1
        Pos2 = InStr(Pos1 + 1, Text, " ")
            
        Do Until Pos1 = 0
            
            If Pos2 = 0 Then 'Pos2 = Len(Text) + 1
                Data(i) = Mid(Text, Pos1 + 1)
            Else
                Data(i) = Mid(Text, Pos1 + 1, Pos2 - 1 - Pos1)
            End If
            
            Pos1 = Pos2
            Pos2 = InStr(Pos1 + 1, Text, " ")
            i = i + 1
            If i > UBound(Data) Then Exit Sub
        Loop
        
    End Sub
    Hope this gives you some ideas.

    There could well be a better more elegant way, if so, will some let us know.

    MTB

Posting Permissions

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