How to Open Files Using VBA
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:
- Team members updating their work status in SharePoint.
- Updating HP Quality Center through an add-in or an Excel macro.
- 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.
- Maintaining a skillset database for students and pulling this data from several workbooks whenever there is a need, like a competition or a tournament.
- 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.
Open <path name> For <mode> [Access access] [lock]
Explaining the above parameters:
- <path name> : A mandatory field. It is the name of the file along with details of its extension, drive and directory.
- <mode> : This is a mandatory field. It can be any of the five options below:
- Random – This is the default mode.
- 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:
- Read and write
- Lock : This is also an optional parameter. It can have any of the values below that are restricted on the opened file:
- Lock read
- Lock write
- 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
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.