WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA FreeFile

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

VBA FreeFile.png

Now take a look at the syntax of the 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 know what kind of model we are going to apply. We can use three ways 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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?

You can download this VBA FreeFile Excel Template here – VBA FreeFile Excel Template

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

VBA FreeFile Example 1

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 file mentioned above 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 the variable “FileNumber.”

VBA FreeFile Example 1-1

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.

VBA FreeFile Example 1-2

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 one 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

VBA FreeFile Example 2

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

Visual Basic Free File Example 2-1

It says 1 as usual.

Now I will progress to the next level.

Visual Basic Free File Example 2-2

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 the FreeFile function in excel VBA  to returns the unique integer number to the file, which is opened along with practical examples and a downloadable excel template. Below you can find some useful excel VBA articles –

  • VBA Counter
  • VBA Text Function
  • VBA Val Function
  • VBA GetObject Function
  • VBA MsgBox (Yes/No)
5 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA FreeFile Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More