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

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
72
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.
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,913
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
72
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,913
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,913
No problem. I Should have asked the type of textbox instead of assuming.
 

Forum statistics

Threads
1,078,504
Messages
5,340,777
Members
399,395
Latest member
KJAC

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top