How to use FreeFile function in VBA?

The FreeFile function in VBA (Visual Basic for Applications) is used to provide a file number that is not currently being used by any other open file. This function helps in managing file IO operations, ensuring that you don’t accidentally overwrite existing file streams.

Here’s a step-by-step guide

Declare File Number Variable: Before you open a file, you need to first obtain a free file number using FreeFile.
Open the File: Use the file number with the Open statement to open a file for input/output operations.
Perform File Operations: Once the file is open, read from or write to the file as needed.
Close the File: After finishing file operations, always close the file to free up system resources.

Example

VBA


Sub WriteToFile()
    Dim fileNum As Integer
    Dim filePath As String
    Dim fileContent As String
    
    ' Specify the path to the file
    filePath = "C:MyFolderMyTextFile.txt"
    
    ' Prepare the content to write
    fileContent = "Hello, World!"
    
    ' Get an unused file number
    fileNum = FreeFile
    
    ' Open the file for output (will overwrite if the file already exists)
    Open filePath For Output As #fileNum
    
    ' Write the content to the file
    Print #fileNum, fileContent
    
    ' Close the file
    Close #fileNum
End Sub

This subroutine opens a file for output and writes “Hello, World!” to the specified text file. If the file specified by filePath already exists, it will be overwritten. If you want to append to the file without overwriting existing content, you would use For Append instead of For Output. Please be sure to adjust the filePath variable with the correct file path for your system, and ensure the VBA code has appropriate permissions to write to the directory.

facebooklinkedin