VBA Text Files

VBA Text Files

We can also read Excel File and write the contents of the cell into a Text File. This way, VBA allows users to work with text files. We can work with test files using two methods

• File System Object
• using Write Command

Using File System Object(FSO)

As the name suggests, FSO Objects help the developers to work with drives, folders and files. In this section, we will discuss how to use FSO.

Object Type	Description
Drive	Drive is an Object. Contains methods and properties that allow you to gather information about a drive attached to the system
Drives	Drives is a Collection. It Provides a list of the drives attached to the system, either physically or logically.
File	File is an Object. It Contains methods and properties that allow developers to create, delete or move a file.
Files	Files is a Collection. It Provides a list of all files contained within a folder.
Folder	Folder is an Object. It Provides methods and properties that allow developers to create, delete or move folders.
Folders	Folders is a Collection. It Provides a list of all the folders within a Folder.
TextStream	TextStream is an Object. It enables 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. The Following properties are supported by Driveobject:

• 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 below :

Step 2 : Add "Microsoft Scripting RunTime" and Click OK.

Step 3 : Add Data that you would like to write it to 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 below under "D:\Try".

The Contents of the file is also shown below :

Using Write Command

unlike FSO, we need NOT add any references, however we will NOT be able to work Drives, Files and Folders. We will be able to just add the stream to text file.

Example

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

Output

Upon executing the script, the "write.txt" file is created in the location "D:\Try" as shown below.

The Contents of the file is also shown below :

← Previous Page

Next Page →