VBA userform textbox to textfile and back

jam1531

New Member
Joined
Jan 5, 2015
Messages
24
Hello,
Stumped on this one. I have a userform with a textbox to input item number(s) and two option buttons "upload" and "retrieve". when the code is run I would like it to doe the following:

If optionbutton1.value = true then

I'd like it to take the item number(s) to be put into a textfile (C:\Users\jmckibb\Desktop\My Stuff\text.txt) and include the Environ("username") and today's date in three columns, for example:

Item_NbrUserIDDate
123456789klj100112/19/18

<tbody>
</tbody>
The catch is if the item number already exists on the sheet then the userID and name need to be replaced with the current Environ("username") and today's date. This way we always have the last to update but do not have duplicates.

Elseif optionbutton2.value = true then
a new workbook is created and the texfile is imported into sheet1 into columns 1, 2 and 3.

End if

Any help appreciated. Thanks!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,826
Office Version
2007
Platform
Windows
The txt file is empty and only one record is going to be placed or the txt file has several records and a new record is going to be appended.

The catch is if the item number already exists on the sheet then the userID and name need to be replaced with the current Environ("username") and today's date. This way we always have the last to update but do not have duplicates.
What is the name of the sheet and in which column to look for the Item.
If the Item exists, where the user and date are replaced: on the sheet or in the txt file?
 

jam1531

New Member
Joined
Jan 5, 2015
Messages
24
Thanks Dante. Sorry for not being more specific. I should have said it better. The user will paste in one or more item numbers into the userform textbox. Then when the macro is ran and optionbutton1, "Upload" is selected it will open the textfile and for each item number in the userform textbox it will check to see if the item number is already on that textfile. If yes, then it will update username and date, if not it will add the item number, username and date.

So in short, on the text file it will will either add or replace all of the item numbers that were originally put in the userform textbox.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,826
Office Version
2007
Platform
Windows
The user will paste in one or more item numbers into the userform textbox.
more item number into textbox, could you put an example, each item number is separated by comma, space, something?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
How do you paste in more then one item into a textbox

So you manually copy something then click on the textbox and manually do a paste.
Then you do another copy and another paste into the same textbox?

The second paste will override the first paste???
 

jam1531

New Member
Joined
Jan 5, 2015
Messages
24
I am having trouble learning how to post pictures on here so it's a bit hard to give an example, but there is a comma between each item number in the textbox. I will figure out how to post small screen shots and give you more detail.
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,278
Adding a picture is not much help.

So are you saying your copying something like this

Mom,Dad,Bob,George,David

Into the Textbox?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,826
Office Version
2007
Platform
Windows
Put next code in your commanbutton.


Code:
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    nItems = TextBox1.Value                         'yout textbox
    If nItems = "" Then
        MsgBox "Insert item number(s)"
        Exit Sub
    End If
    '
    'ruta = "C:\trabajo\"
    ruta = "C:\Users\jmckibb\Desktop\My Stuff\"     'path file
    arch = "text.txt"                               'file txt
    user = CreateObject("WScript.Network").UserName
    fech = Format(Date, "mm/dd/yy")
    '
    If OptionButton1.Value = True Then
        items = Split(nItems, ",")
        Workbooks.OpenText Filename:=ruta & arch, Origin:=xlMSDOS, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
            ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False, _
            FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
            TrailingMinusNumbers:=True
        Set l2 = ActiveWorkbook
        Set h2 = l2.Sheets(1)
        For i = LBound(items) To UBound(items)
            citems = WorksheetFunction.Trim(items(i))
            If IsNumeric(citems) Then
                citems = Val(citems)
            End If
            Set b = h2.Columns("A").Find(citems, lookat:=xlWhole)
            If Not b Is Nothing Then
                h2.Cells(b.Row, "B").Value = "'" & user
                h2.Cells(b.Row, "C").Value = "'" & fech
            Else
                u = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
                h2.Cells(u, "A").Value = "'" & citems
                h2.Cells(u, "B").Value = "'" & user
                h2.Cells(u, "C").Value = "'" & fech
            End If
        Next
    End If
    h2.Columns("A:C").EntireColumn.AutoFit
    For i = 1 To 3
        h2.Columns(i).ColumnWidth = Int(h2.Cells(1, i).ColumnWidth + 1)
    Next
    l2.SaveAs Filename:=ruta & arch, _
        FileFormat:=xlTextPrinter, CreateBackup:=False
    l2.Close False
    MsgBox "Updated text file"
End Sub
Regards Dante Amor
 
Last edited:

Forum statistics

Threads
1,082,048
Messages
5,362,890
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top