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

[ajay_gajree]

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]

Try...

    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]

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

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