Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Saving Information in a Text File.
There may be times when you want a macro to save information to a text file. This is very easy to do. All you need is to open the file for output, and then start sending information to the file. The following code fragment writes a text file using this method.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Open "MyFile.Dat" For Output As #1
Print #1, NumValues
For J = 1 to NumValues
Print #1, UserVals(J)
Next J
Close #1
The first thing written to the file is a numeric value indicating how many individual values will follow it (the code presumes that you set this value in the NumValues variable). Then a For ... Next loop is used to create the balance of the file.
You should be aware that the code, as written, will overwrite any existing MyFile.Dat file. If you want to protect the file in some way, your code will need to check to see if it exists before opening it or you'll need to set the attributes of the file to read-only.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8885) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Saving Information in a Text File.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
You can use macros to make your common Excel tasks easier and faster. For instance, if you routinely need to create new ...
Discover MoreNeed to pull a list of words from a range of cells? This tip shows how easy you can perform the task using a macro.
Discover MoreWhen you use a macro to do file operations, it works (by default) within the current directory. If you want to know which ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-11-01 17:12:40
Allen
Instead of using "For Output" in the "Open" line, use "For Append"
-Allen
2020-11-01 16:06:50
John Mann
How would you append the new data to an existing text file, rather than overwriting what ever is in the file?
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments