Handling other files in VBA macros

As a programmer, you’re likely to come across many situations where other files need to be opened for reading, writing, or appending data. A macro can help you easily handle these files, and the Open method is your ticket.

Examples of real-life scenarios for file handling

Here are some applications where you can put this method to use:

1. Team members updating their work status in SharePoint.
2. Updating HP Quality Center through an add-in or an Excel macro.
3. A schoolteacher marking the list of absentees in an Excel sheet and using an add-in (macro) that automatically fills in the attendance for all students in the class.
4. Maintaining a skillset database for students and pulling this data from several workbooks whenever there is a need, like a competition or a tournament.
5. Companies creating and send quotes by just filling out a form. Templates can also be used along with macros for this.

The VBA Open File method

VBA offers simple method to open and work on files. This permits a user to either read or write — or do both — after opening the file.

Syntax:

Open <path name> For <mode> [Access access] [lock]

Explaining the above parameters:

1. <path name> : A mandatory field. It is the name of the file along with details of its extension, drive and directory.
2. <mode> : This is a mandatory field. It can be any of the five options below:
   1. Append
   2. Binary
   3. Output
   4. Input
   5. Random – This is the default mode.
3. Access :  This is an optional parameter. It lists out the operations that are allowed on the open file. It can be any one of the following:
   1. Read
   2. Write
   3. Read and write
4. Lock : This is also an optional parameter. It can have any of the values below that are restricted on the opened file:
   1. Shared
   2. Lock read
   3. Lock write
   4. Lock read-write

Note: Tags within square brackets are optional. Here the access and lock tags are optional.

Examples of opening files using VBA

Example 1: Just open a file

This is a simple program which simply opens an Excel file.

Sub open_file_demo()

' declare variable
Dim pathname

' assign a value
pathname = "C:\Users\LAKSHMI RAMAKRISHNAN\Downloads\Project 1.xlsx"

' now open the file using the open statement
Workbooks.Open pathname

End Sub

Example 2: Open an Excel workbook in “read only” mode and try to write to it

Sub open_file_demo()

' declare variables
Dim pathname
Dim wb As Workbook

' assign a value
pathname = "C:\Users\LAKSHMI RAMAKRISHNAN\Downloads\Project 1.xlsx"

' now open the file using the open statement and assign it to the wb object so that it can be used in the code further.
Set wb = Workbooks.Open(Filename:=pathname, ReadOnly:=True)

' Try writing after opening the file in "read only mode". This should throw an error.
wb.Sheets(0).Cells(1, 1).Value = "Try writing"

End Sub

Example 3: Open a text file and read its contents using the Open function and file number

Sub TextFile_PullData()

' declare variables

Dim int_txtfile As Integer
Dim str_file_Path As String
Dim str_File_Content As String

' Assign the file path to the variable
  str_file_Path = "C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\sample.txt"

' Find the next available file number to be used by the fileopen function
  int_txtfile = FreeFile

' Open the said text file using the function
  Open str_file_Path For Input As int_txtfile

' The content of the file is stored in a variable
  str_File_Content = Input(LOF(int_txtfile), int_txtfile)

' Print the file content using the variable in which it is stored
  Debug.Print str_File_Content

' Close the opened text file
  Close int_txtfile

End Sub

Example 4: Read and then write content to a text file

In this example, we first open a text file in read mode and copy its content to a variable. Then we modify its content and open the same text file using write mode. Finally we write the modified content to the file.

Sub txt_file_FindReplace()

Dim txt_file As Integer
Dim file_path As String
Dim file_content As String

' Assign the file path to the variable
  file_path = "C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\sample.txt"

' Determine the next available file number to be used by the FileOpen function
  txt_file = FreeFile

'  The text file is opened in read-only mode
  Open file_path For Input As txt_file

' Store the content of the file in a variable
  file_content = Input(LOF(txt_file), txt_file)

' Close the opened Text File
  Close txt_file
  
' Find and replace some text
  file_content = Replace(file_content, "It", "This file")

' Determine the next available file number to be used by the FileOpen function
  txt_file = FreeFile

' Open the text file in a Write State
  Open file_path For Output As txt_file
  
' Write the modified content to the file
  Print #txt_file, file_content

' Close the opened Text File
  Close txt_file

End Sub

Example 5: Append data to a text file

In this example, we will add additional text to the end of an existing text file that already has some data.

Sub txt_file_append()

' declare variables

Dim int_txtfile As Integer
Dim str_file_Path As String
Dim str_File_Content As String

' Assign the file path to the variable
  str_file_Path = "C:\Users\LAKSHMI RAMAKRISHNAN\OneDrive\Documents\sample.txt"

' Find the next available file number to be used by the fileopen function
  int_txtfile = FreeFile

' Open the said text file using the function
  Open str_file_Path For Append As int_txtfile

' Write content to the end of the file
  Print #int_txtfile, "This is additional content"
  Print #int_txtfile, "Warm Regards"
  Print #int_txtfile, "Laksmi Ramakrishnan"

' Close the opened text file
  Close int_txtfile

End Sub

Conclusion

Though there are many ways to open files with different extensions using VBA, in this article we have tried to focus on the most straightforward or simple methods of doing so.

The “open” mode in this openfile method plays a vital role here. If we try to append or write to a file that is opened using read mode, an error is thrown, halting the program’s run. So you can think of read mode as providing “data security” to the original file that is opened.