Microsoft Office Tutorials and References
In Depth Information
Using Text Files to Store External Data
using TExT filEs To sTorE ExTErnAl dATA
Hail the text file, the true foot soldier interface for transferring information between two or more
otherwise disparate platforms. In the modern age of computing, it’s always been the text file that
could be relied on for one application downloading its information in a comma-delimited or
ixedlength file, and another application like Excel being able to accept the data.
Text files are not pretty, they are almost never formatted, and they are not easy to read. But when all
else fails, they come through and are fairly easy to program. The following examples show how text
files can help you in your everyday work.
Suppose you want Excel to add a new record to a text file that records the date and time a particular
Excel workbook was saved. In the folder YourFilePath is a text file named LogFile.txt . The
following VBA code goes into the ThisWorkbook module of the Excel file you are monitoring:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim intCounter As Integer, myFileName As String
myFileName = “C:\YourFilePath\LogFile.txt”
intCounter = FreeFile
Open myFileName For Append As #intCounter
Write #intCounter, ThisWorkbook.FullName, Now, Application.UserName
Close #intCounter
End Sub
This macro will create four new text files, naming each with the prefix MyFile , followed by a
number suffix in order from 1 to 4. For example, the first file will be named MyFile001.txt , the second
file will be named MyFile002.txt , and so on. The starting number of 1 is derived by the code line
For intCounter = 1 to 4 . If you wanted to create four new text files starting with the name
MyFile038.txt , you’d establish the starting number of 38 by specifying it with the line of code For
intCounter = 38 to 41 .
Sub CreateTextFiles()
Dim intCounter As Integer, strFile As String
For intCounter = 1 To 4
strFile = “MyFile” & Format(intCounter, “000”)
strFile = “C:\YourFilePath\” & strFile & “.txt”
Open strFile For Output As #1
Close
Next intCounter
End Sub
The following macro will copy the text of your comments in your worksheet’s used range into a text
file, where they will be listed along with the cell values in that range. This is a very fast macro.
Sub Comment2Text()
Dim cmt As Comment, rng As Range
Dim iRow As Long, iCol As Long
Dim strText As String
Search JabSto ::




Custom Search