replacing value in a flat file copied from excel

Alexxx12 · #1 (**permalink**) 07-25-04, 11:31

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:MM

S") & " " _
& Format(Range("i4").Value, "HH:MM

S") & " " _
& Format(Range("J4").Value, "HH:MM

S")
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

MikeTheBike · #2 (**permalink**) 07-26-04, 11:24

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

Internet Services

Web Development

Digital Marketing

Consumer Tech