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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

gallen

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

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,659
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top