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!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,046
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!
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,984
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,086,234
Messages
5,388,611
Members
402,127
Latest member
BiscuitButter

Some videos you may like

This Week's Hot Topics

Top