# Update text file with excel user form data

#### Mike_CS

##### New Member
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.

Many thanks in advance Excelling Excellers

Last edited:

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### John_w

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

Code:
Public Sub Update_Text_File()

Dim textFilePath As String
Dim records As Variant, r As Long, fields As Variant, c As Long
Dim findColumnHeading As String, newColumnValue As String
Dim fileNum As Integer

textFilePath = "C:\path\to\Text_File.txt"    'CHANGE - FULL TEXT FILE NAME
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

'Look for username in 1st column of records

For r = 1 To UBound(records)
fields = Split(records(r), ",")
For c = 1 To UBound(columnHeadings)
'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

##### New Member
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>