Excel 2016: Excel VBA, Copy Userform Textbox.value to a new text file

ajay_gajree

Well-known Member
Joined
Jul 16, 2011
Messages
518
Hi All

Can someone tell me how to copy a Userform Textbox.value to a new text file using VBA?

I would like to save the new text file to the current directory

Any pointers welcome!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Try...

Code:
    Dim saveToDirectory As String
    Dim saveAsFilename As String
    Dim fileNumber As Integer
    
    saveToDirectory = CurDir & "\"
    saveAsFilename = "sample.txt" 'change the file name accordingly
    
    fileNumber = FreeFile()
    Open saveToDirectory & saveAsFilename For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] 
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] , Me.TextBox1.Value
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL]

Note that if a file with the same name already exists, it will be overwritten.

Hope this helps!
 
Upvote 0
Another way (which also overwrites existing file)
Before running the code Go to VBA \ Tools \ References \ check box for "Microsoft Scripting Runtime"
I have assumed current directory is the folder of the active workbook (amend if that is not what you want)

In Userform code
Code:
Option Explicit

Private Sub CommandButton1_Click()
    WriteToTextFile (TextBox1.Value)
    Unload Me
End Sub

Private Sub WriteToTextFile(TextBoxText As String)
    Dim filePath As String, fileStream As TextStream, fso As FileSystemObject
    filePath = [COLOR=#000080]ActiveWorkbook.Path[/COLOR] & "\" & "[COLOR=#000080]MyTestFile.txt[/COLOR]"
    Set fso = New FileSystemObject
    Set fileStream = fso.CreateTextFile(filePath)
'write to file
    fileStream.WriteLine TextBoxText
    fileStream.Close
    If fso.FileExists(filePath) Then MsgBox filePath & " was created"
'tidy up
    Set fileStream = Nothing
    Set fso = Nothing
End Sub
refer this webpage
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top