VBA userform textbox to textfile and back

[jam1531]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item_Nbr[/TD]
[TD]UserID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]123456789[/TD]
[TD]klj1001[/TD]
[TD]12/19/18[/TD]
[/TR]
</tbody>[/TABLE]
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]

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]

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]

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]

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]

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.

 

[My Aswer Is This]

Adding a picture is not much help.

So are you saying your copying something like this

Mom,Dad,Bob,George,David

Into the Textbox?

 

[jam1531]

Yep?

 

[DanteAmor]

Put next code in your commanbutton.

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

 

[jam1531]

Perfect! Thanks!