How to use Input # statement in VBA?

The Input # statement in VBA (Visual Basic for Applications) is used for reading data from a file opened in Input mode. It enables you to read strings or variables from a file into your VBA program. Here’s a basic overview of how to use it:

Steps to Use Input #

Open the File: Before you can read from a file, you must open it using the Open statement.
Read Data Using Input #: Once the file is open, you can use Input # to read the data.
Close the File: Always remember to close the file after you’re done reading from it.

Example

Here’s a simple example to illustrate the use of Input #:

VBA

Sub ReadFromFile()
    Dim filePath As String
    Dim fileNum As Integer
    Dim data As String

    filePath = "C:pathtoyourfile.txt"  ' Specify the file path
    fileNum = FreeFile()                   ' Get an available file number

    ' Open the file for input
    Open filePath For Input As #fileNum

    ' Read data from the file
    Input #fileNum, data

    ' Close the file
    Close #fileNum

    ' Display the read data
    MsgBox data
End Sub

In this example:

We define the file path and get a free file number using FreeFile().
We open the file for input.
Using Input #, we read data from the file into the variable data.
After reading, we close the file.
Finally, we display the read data in a message box.

Notes

The Input # statement reads data as it’s formatted in the file, which means it expects the same format as it was written with, typically using Write #.
When reading strings, Input # expects them to be enclosed in quotation marks as they usually are in files written with Write #.
If the data in the file doesn’t match the expected format, an error may occur.
Always handle potential errors, especially when dealing with file operations.
Remember to close the file after finishing the reading process to release the file resources.

facebooklinkedin