VBA FreeFile

Excel VBA FreeFile

FreeFile is a function in VBA that is available only as a VBA function not as a worksheet function. VBA FreeFile function returns the unique integer number to file which is opened and preserves the number for the next available file number.

We usually open files from our computer to either write something or to read-only, while referring to those files we must refer with a unique integer number. VBA FreeFile function allows us to determine that unique integer number to assign to opening the file to read, write, and open files using VBA.

Now take look at the syntax of OPEN statement.

OPEN [File Path Address] For [Mode to Open] As [File Number]

File Path Address: We need to mention the file address on our computer that we are trying to open.

Mode to Open: While opening the file we need to aware of what kind of model we are going to apply. We can use three modes here, “Input Mode”, “OutPut Mode”, and “Append Mode”.

Input mode to read only the file.

Output mode to wipe out the existing data and insert new data.

Append mode to add new data while retaining the existing data.

File Number: With this argument, we can refer to the file we are opening. This where the “FreeFile” function plays a vital role and returns the unique integer number.

How to Use the FreeFile Function in Excel VBA?

Now, look at the below code.

Code:

Sub FreeFile_Example1()

 Dim Path As String
 Dim FileNumber As Integer

 Path = "D:\Articles\2019\File 1.txt"
 FileNumber = FreeFile

 Open Path For Output As FileNumber

 Path = "D:\Articles\2019\File 2.txt"
 FileNumber = FreeFile

 Open Path For Output As FileNumber

End Sub

Now let me decode the above code for you to understand.

First I have declared two variables.

Dim Path As String, Dim FileNumber As Integer

Then I have assigned the file path with its name.

Path = "D:\Articles\2019\File 1.txt"

Then for the one more variable, I have assigned the FREEFILE function.

FileNumber = FreeFile

Then I have used the Open statement to open the text file in the above-mentioned file path.

Open Path For Output As FileNumber

Ok, now I will run line by line code by pressing the F8 key and see the value of variable “FileNumber”.

It is showing the file number as 1. So, the free file function automatically reserves this number to the opening file. While running this no other files are opened.

Now I will keep executing the next line of VBA code and see what the file number is if I jump to the next line.

Now it says 2. So FreeFile function reserves the unique integer number 2 to the second opening file.

FreeFile Function Always Returns 1 if we Close the Excel File

One thing we need to look at is VBA “FreeFile” function always returns 1 if we close the opened file before opening the second excel file.

For example, look at the below code.

Code:

Sub FreeFile_Example2()

  Dim Path As String
  Dim FileNumber As Integer

  Path = "D:\Articles\2019\File 1.txt"
  FileNumber = FreeFile

  Open Path For Output As FileNumber
  Close FileNumber

  Path = "D:\Articles\2019\File 2.txt"
  FileNumber = FreeFile

  Open Path For Output As FileNumber
  Close FileNumber

End Sub

Now I will once again execute the code line by line by pressing the F8 key.

It says 1 as usual.

Now I will progress to the next level.

Even in the second attempt to it says 1.

The reason for this is since we have used the Close file statement FreeFile recognizes the newly opened file as the fresh one and returns the integer number as 1.

Recommended Articles

This has been a guide to VBA FreeFile. Here we discuss how to use FreeFile function in excel VBA  to returns the unique integer number to file which is opened along with practical examples and downloadable excel template. Below you can find some useful excel VBA articles –

• VBA Counter
• VBA Text Function
• VBA Val Function
• VBA GetObject Function