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.

S.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 list 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 list 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 list 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.

Excel FSO in VBScript

Step 2 − Add “Microsoft Scripting RunTime” and Click OK.

Excel FSO in VBScript

Step 3 − Add Data that you would like to write in a Text File and add a Command Button.

Excel FSO in VBScript

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”.

Excel FSO in VBScript

The Contents of the file are shown in the following screenshot.

Excel FSO in VBScript

Write Command

Unlike 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_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 in the following screenshot.

Excel FSO in VBScript

The contents of the file are shown in the following screenshot.

Excel FSO in VBScript

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s