Read and Write Text File using Visual Basic for Applications (VBA)

Windows Filing

In this article we will learn how we can read/write a text file using Windows filing which is a memory based object. Followings are the methods which we use to manipulate a file:

• For Output – This is exclusive write mode, When you are opening the text file with this command, you are wanting to create or modify the text file.
• For Input – This method allows user to open a file in readonly mode and read the text.
• For Append – Add new text to the bottom of your text file content.
• FreeFile – Is used to supply a file number that is not already in use. The function will automatically return the next available reference number for your text file. 
• Write – This writes a line of text to the file surrounding it with double course (“”)
• Print – This writes a line of text to the file without double course

Write file code example

Sub CreateFile()
    Dim oTextFile As Integer
    Dim FilePath As String

    'File path and name
    FilePath = "C:\Users\Downloads\testFile.txt"

    'Determines the next file number available for use by the FileOpen function
    oTextFile = FreeFile

    'Open the text file
    Open FilePath For Output As oTextFile

    'Write some lines of text
    Print #oTextFile, "https://VBAOVERALL.COM"
    Print #oTextFile, "VBAOVERALL is a child of InfoExtract Private Limited"
    Print #oTextFile, "For more info please visit www.infoextract.in"
      
    'Save & Close Text File
    Close oTextFile

End Sub

Output

Read file code example

Sub ReadFile()
    Dim oTextFile As Integer
    Dim FilePath As String

    'File path and name
    FilePath = "C:\Users\Downloads\testFile.txt"

    'Determines the next file number available for use by the FileOpen function
    oTextFile = FreeFile

    'Open the text file
    Dim oLine As String
    Open FilePath For Input As oTextFile
    'Read file till end line by line
    Do While Not EOF(oTextFile)
        'Read line
        Line Input #oTextFile, oLine
        'Print line
        Debug.Print oLine
    Loop
    
    'Another example
    'Store entire file contents in a variable
    oFileText = Input(LOF(oTextFile), oTextFile)

    'Report Out Text File Contents
    Debug.Print oFileText
      
    'Save & Close Text File
    Close oTextFile

End Sub

Append file code example

Sub AppendFile()
    Dim oTextFile As Integer
    Dim FilePath As String

    'File path and name
    FilePath = "C:\Users\Downloads\testFile.txt"

    'Determines the next file number available for use by the FileOpen function
    oTextFile = FreeFile

    'Open the text file
    Dim oLine As String
    Open FilePath For Append As oTextFile
    
    'Append file
    Print #oTextFile, "This is new text"
    
      
    'Save & Close Text File
    Close oTextFile

End Sub

Output

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Share on Facebook

Tweet