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 | DriveDrive is an Object. Contains methods and properties that allow you to gather information about a drive attached to the system. |
| 2 | DrivesDrives is a Collection. It provides a list of the drives attached to the system, either physically or logically. |
| 3 | FileFile is an Object. It contains methods and properties that allow developers to create, delete, or move a file. |
| 4 | FilesFiles is a Collection. It provides a list of all the files contained within a folder. |
| 5 | FolderFolder is an Object. It provides methods and properties that allow the developers to create, delete, or move folders. |
| 6 | FoldersFolders is a Collection. It provides a list of all the folders within a folder. |
| 7 | TextStreamTextStream 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 Click OK.
Step 3 − Add Data that you would like 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_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 = ActiveSheet.UsedRange.Rows.Count
' 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
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.
Next Topic:-Click Here
Pingback: VBA - Excel Objects - Adglob Infosystem Pvt Ltd