VBA - Text files
VBA - Text Files
You can also read Excel file and write cell contents to Text file using VBA. VBA allows users to work with text files using two methods -
- File system object
- using the d command 'write
File System Object (FSO)
As the name suggests, FSOs help developers work with drives, folders, and files. In this section we will see how to use an FSO.
| Sr.No. || Type of object and deion |
| 1 || |
Drive is an object. Contains methods and properties that allow you to collect information about a drive connected to the system.
| 2 |
Drives is a collection. It provides a list of drives connected to the system, physically or logically.
| 3 || |
The file is an object. It contains methods and properties that allow developers to create, delete, or move a file.
| 4 || |
Files are a collection. It provides a list of all the files contained in a folder.
| 5 || |
The folder is an object. It provides methods and properties that allow developers to create, delete, or move folders.
| 6 || |
Folders are a collection. It provides a list of all the folders in a folder.
| 7 || |
TextStream is an object. It allows developers to read and write text files.
Drive is an object, which allows access to the properties of a particular disk drive or network share. The following properties are supported by the Drive -
- DriveLetter li object >
Step 1 - Before moving on to using FSO, we need to enable Microsoft Scripting Runtime. To do the same, go to Tools → References as shown in the following screenshot.
Step 2 - Add "Microsoft Scripting RunTime " and click on OK.
Step 3 - Add the data that you want to write to a text file and add a command button.
Step 4 - Now is Script time.
Private Sub fn_write_to_text_Click () Dim FilePath As String Dim CellData As String Dim LastCol As Long Dim LastRow As Long Dim fso As FileSystemObject Set fso = New FileSystemObject Dim stream As TextStream LastCol = ActiveSheet.UsedRange .Columns.Count LastRow.Columns.Count LastRow = ActiveSheetRowsange.UsangeR 'Create a TextStream. Set stream = fso.OpenTextFile ( "D: Try Support.log ", ForWriting, True) CellData = "" For i = 1 To LastRow For j = 1 To LastCol CellData = Trim (ActiveCell (i, j) .Value) stream.WriteLine "The Value at location (" & i & ", " & j & ") " & CellData Next j Next i stream.Close MsgBox ( "Job Done ") End Sub
When running, make sure to place the cursor in the first cell of the worksheet. The Support.log file is created as shown in the following screenshot under "D: Try ".
The contents of the file are shown in the following screenshot .
Unlike FSO, we do NOT need to add references, however, we will NOT be able to work on it.with drives, files and folders. We can just add the feed to the text file.
Private sub fn_write_to_text_Click () Dim FilePath As String Dim CellData As String Dim LastCol As Long Dim LastRow As Long LastCol = ActiveSheet.UsedRange. Columns.Count LastRow = ActiveSheet.UsedRange.Rows.Count FilePath = "D: Try write.txt " Open FilePath For Output As # 2 CellData = "" For i = 1 To LastRow For j = 1 To LastCol CellData = "The Value at location (" & i & ", " & j & ") " & Trim (ActiveCell (i, j) .Value) Write # 2, CellData Next j Next i Close # 2 MsgBox ( "Job Done ") End Sub
When running, the file "write.txt " is created at the location "D: Try " as shown in the following screenshot.
The contents of the file are shown in the following screenshot.