Update text file with excel user form data

[Mike_CS]

Hi All

As usual i'm back with probably what is a really silly easy question.

I have created a user form which has text boxes, labels, comboboxes, checkboxes etc.

I would like to know if there's a way i can use them to update specific records in a text file.

Basically If Column 1 uses the Environ$("UserName"), that will be the Row identifier.
Then i'd really to choose a Row using the UserName and a Column (which is headed in Row 1 of text file) with a combobox value. Sort of a cross reference and update the part of the text file where the row and column meet.

Is that even possible or am i expecting too much from text files and Excel. Have read quite a few postings and none are quite right to what i need.

Or have i missed one and its already been asked and answered by you fine people.

Many thanks in advance Excelling Excellers

 

[John_w]

See if this gets you started. Edit the code where shown. Assumes each record field (column) is separated by a comma.

Public Sub Update_Text_File()

    Dim textFilePath As String
    Dim records As Variant, r As Long, fields As Variant, c As Long
    Dim columnHeadings As Variant
    Dim username As String
    Dim findColumnHeading As String, newColumnValue As String
    Dim fileNum As Integer
    
    username = Environ("username")
    textFilePath = "C:\path\to\Text_File.txt"    'CHANGE - FULL TEXT FILE NAME
    findColumnHeading = "column heading"            'CHANGE - COLUMN HEADING TO FIND
    newColumnValue = "new value"                  'CHANGE - NEW COLUMN VALUE
        
    'Open file and read all records
    
    fileNum = FreeFile
    Open textFilePath For Input As fileNum
    records = Split(Input(LOF(fileNum), fileNum), vbCrLf)
    Close fileNum
    
    'Get column headings from 1st record
    
    columnHeadings = Split(records(0), ",")
    
    'Look for username in 1st column of records
    
    For r = 1 To UBound(records)
        fields = Split(records(r), ",")
        If fields(0) = username Then
            'Found username.  Look for required column heading
            For c = 1 To UBound(columnHeadings)
                If columnHeadings(c) = findColumnHeading Then
                    'Found column heading.  Update the field value for this record
                    fields(c) = newColumnValue
                    'Recreate this record
                    records(r) = Join(fields, ",")
                    Exit For
                End If
            Next
            Exit For
        End If
    Next
    
    'Write records to new file
    
    fileNum = FreeFile
    Open textFilePath For Output As fileNum
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNum]#fileNum[/URL] , Join(records, vbCrLf)
    Close fileNum
    
End Sub

 

[Mike_CS]

Thats brilliant John thank you very much. i Just changed the ColumnHeading to a label caption (Date) NewColumnValue to a combobox value (Leave Type) and it works perfectly and faster than i thought.

As I have 2 leave text files. One with the users going down in first column, and dates going across Top Row. The other has dates going down and users across (havent got the transpose data bit worked out yet) but made 2 subs from the above and everything is working great.

Have used this for a Leave sheet so users can only see their own data in a listbox but update using the combobox and labels.

Next on my list is getting the listbox to populate with only dates with entries in for the user.

I'm going to have a tinker with this and see if i can update multple columns in one swoop as i have a skills table with users going down and Skill types going across but the userform has (on some multipage tabs) up to 28 checkbox tabs for a skill.

This is a great starting point though. Thanks again.

I started learning VBA, then SQL, then Access and now text files. Talk about backward thinking.<strike></strike>