- VBA - Userforms
- VBA - Programming Charts
- VBA - Text Files
- VBA - Excel Objects
- VBA - Error Handling
- VBA - Events
- VBA - Sub Procedure
- VBA - Functions
- VBA - Arrays
- VBA - Date and Time
- VBA - Strings
- VBA - Loops
- VBA - Decisions
- VBA - Operators
- VBA - Constants
- VBA - Variables
- VBA - Input Box
- VBA - Message Box
- VBA - Macro Comments
- VBA - Excel Terms
- VBA - Excel Macros
- VBA - Overview
- VBA - Home
VBA Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
VBA - Text Files
You can also read Excel File and write the contents of the cell into a Text File using VBA. VBA allows the users to work with text files using two methods −
File System Object
using Write Command
File System Object (FSO)
As the name suggests, FSOs help the developers to work with drives, folders, and files. In this section, we will discuss how to use a FSO.
Sr.No. | Object Type & Description |
---|---|
1 |
Drive Drive is an Object. Contains methods and properties that allow you to gather information about a drive attached to the system. |
2 |
Drives Drives is a Collection. It provides a pst of the drives attached to the system, either physically or logically. |
3 |
File File is an Object. It contains methods and properties that allow developers to create, delete, or move a file. |
4 |
Files Files is a Collection. It provides a pst of all the files contained within a folder. |
5 |
Folder Folder is an Object. It provides methods and properties that allow the developers to create, delete, or move folders. |
6 |
Folders Folders is a Collection. It provides a pst of all the folders within a folder. |
7 |
TextStream TextStream is an Object. It enables the developers to read and write text files. |
Drive
Drive is an object, which provides access to the properties of a particular disk drive or network share. Following properties are supported by Drive object −
AvailableSpace
DriveLetter
DriveType
FileSystem
FreeSpace
IsReady
Path
RootFolder
SerialNumber
ShareName
TotalSize
VolumeName
Example
Step 1 − Before proceeding to scripting using FSO, we should enable Microsoft Scripting Runtime. To do the same, navigate to Tools → References as shown in the following screenshot.
Step 2 − Add "Microsoft Scripting RunTime" and Cpck OK.
Step 3 − Add Data that you would pke to write in a Text File and add a Command Button.
Step 4 − Now it is time to Script.
Private Sub fn_write_to_text_Cpck() 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 = ActiveSheet.UsedRange.Rows.Count Create a TextStream. Set stream = fso.OpenTextFile("D:TrySupport.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
Output
When executing the script, ensure that you 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.
Write Command
Unpke FSO, we need NOT add any references, however, we will NOT be able to work with drives, files and folders. We will be able to just add the stream to the text file.
Example
Private Sub fn_write_to_text_Cpck() 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:Trywrite.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
Output
Upon executing the script, the "write.txt" file is created in the location "D:Try" as shown in the following screenshot.
The contents of the file are shown in the following screenshot.
Advertisements