Saving data from textbox to specific folder without dialog as .txt

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81
Hi.

I am trying to save information within a textbox, to a .txt file, to a spesific folder, without using dialog (this button shall basicly autosave the information within the textbox to a folder, with only saying something like "Submitted" when its done)

I have only used msofiledialogfolderpicker etc before, and not directly "autosaving".
Is there a good way to do that?

Also, when saving the file as .txt, i also want it to incrementaly increase the file name. Example, first is 1.txt next is 2.txt etc etc (Or even better for my case, if its possible to format the "name.txt" to yymmddmmss (year, month,date,minute,second)

Can anyone help me with this ? :)
Thanks in advance.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
Hello. This code will record the text in a shape called "Textbox 1" on a worksheet, and save it to a text file in a given folder with the requested format

3 things need updating by you:


  • The sheet name (I've used "Sheet1" as default) needs updating


  • I've assumed the textbox is the only one and will have been defaultly named "Textbox 1"


  • The path to the folder to store the file needs updating ("shown as "H:\Test". Remember the final backslash!)


Code:
Sub SaveText()

    Dim tb As Shape 'text box shape
    Dim sText As String 'text in the textbox
    Dim sPath As String 'path of folder for the text file to be held
    Dim sName As String 'name of file
    
    On Error GoTo errHandle
    
    'set the path
    sPath = "H:\Test\" 'Change accordingly
    
    'set the textbox
    Set tb = Sheet1.Shapes("Textbox 1")
    
    'get the name of file in the format "yymmddmmss"
    sName = Format(Now, "yymmdd") & Format(Minute(Now), "00") & Format(Second(Now), "00") & ".txt"
    
    Open sPath & sName For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    
        Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , WorksheetFunction.Clean(tb.TextFrame.Characters.Text)
        
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    
    MsgBox "Success!", vbInformation, "Textbox Text Saved"

Exit Sub

errHandle:
    MsgBox Err.Description, vbCritical, "Failed"
End Sub
 
Last edited:

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81
Hi, and thanks for the reply :)
I forgot to mention, that its a textbox from a userform i am trying to save as .txt.
Noticed that your code had sheet and shape definitions :)


Hello. This code will record the text in a shape called "Textbox 1" on a worksheet, and save it to a text file in a given folder with the requested format

3 things need updating by you:


  • The sheet name (I've used "Sheet1" as default) needs updating


  • I've assumed the textbox is the only one and will have been defaultly named "Textbox 1"


  • The path to the folder to store the file needs updating ("shown as "H:\Test". Remember the final backslash!)


Code:
Sub SaveText()

    Dim tb As Shape 'text box shape
    Dim sText As String 'text in the textbox
    Dim sPath As String 'path of folder for the text file to be held
    Dim sName As String 'name of file
    
    On Error GoTo errHandle
    
    'set the path
    sPath = "H:\Test\" 'Change accordingly
    
    'set the textbox
    Set tb = Sheet1.Shapes("Textbox 1")
    
    'get the name of file in the format "yymmddmmss"
    sName = Format(Now, "yymmdd") & Format(Minute(Now), "00") & Format(Second(Now), "00") & ".txt"
    
    Open sPath & sName For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    
        Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , WorksheetFunction.Clean(tb.TextFrame.Characters.Text)
        
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    
    MsgBox "Success!", vbInformation, "Textbox Text Saved"

Exit Sub

errHandle:
    MsgBox Err.Description, vbCritical, "Failed"
End Sub
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
OK this code activates on the press of commandbutton1

Change name of commandbutton and the Textbox in this code:

Code:
Sub SaveText()


    Dim sText As String 'text in the textbox
    Dim sPath As String 'path of folder for the text file to be held
    Dim sName As String 'name of file
    
    On Error GoTo errHandle
    
    'set the path
    sPath = "H:\Test\" 'Change accordingly
    
    'get the name of file in the format "yymmddmmss"
    sName = Format(Now, "yymmdd") & Format(Minute(Now), "00") & Format(Second(Now), "00") & ".txt"
    
    Open sPath & sName For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    
        Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , WorksheetFunction.Clean(TextBox1.Text) ' Change 'Textbox1' to the name of your textbox
        
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
    
    MsgBox "Success!", vbInformation, "Textbox Text Saved"

Exit Sub

errHandle:
    MsgBox Err.Description, vbCritical, "Failed"
End Sub


Private Sub CommandButton1_Click()
    SaveText
End Sub
 
Last edited:

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
No problem. I Should have asked the type of textbox instead of assuming.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,123
Messages
5,412,587
Members
403,433
Latest member
DavidCB

This Week's Hot Topics

Top