Excel VBA Input Function
In this article, we will see an outline on Excel VBA Input. Input is a function in VBA that is used to read the files whether it is opened in binary or input mode. The result of this function is a string that returns the contents of the file.
Syntax of the Input Function:
Input ( number, [#]filenumber )
These are the required parameters for this function. When we use this function to read a file we also use another function which is known as EOF or End of File function which reads the contents of the file until the end is reached. Also in this input function, the contents of the files are read as rows.
How to Use the Input Function in Excel VBA?
Input function in VBA is a statement whose one mandatory argument is to provide it with the file number. There can be many files opened at once in VBA but they are required to be in sequence or given a file number. Even if there is a single file we need to number it as #1. The input statement is used on the file and also on the contents of the file, let’s see this through a few examples.
VBA Input – Example #1
For this example, I have a text file on my desktop with some data in it. In this example, we will use the input function to read the data in the text file in the notepad document. For this, follow the below steps:
Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.
Step 2: Once the new module is inserted we can start with our subprocedure for this example.
Code:
Sub Example() End Sub
Step 3: In order to read the path of the file we need to declare a variable for it, as the path does contain characters let us declare the path as a string.
Code:
Sub Example() Dim Path As String End Sub
Step 4: The next step is to give the variable the path of a text file, in order to get that right-click on the file on the desktop and click on properties and then on security we can see the file path as the object.
Step 5: Assign the path to the variable for the path in the code.
Code:
Sub Example() Dim Path As String Path = "C:\Users\cba_16\Desktop\text.txt" End Sub
Step 6: Let us open our file path with the Open statement and we need to specify how we want to open the file, we want to open the file with input because we want to read from the file.
Code:
Sub Example() Dim Path As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input End Sub
Step 7: So we have to open a single file or multiple files we need to provide it a number. since this is the first file we want to open for Input we will give it a number 1 with the character hashtag or #.
Code:
Sub Example() Dim Path As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 End Sub
Step 8: Like every statement in this open statement when we have opened up our file we want to close the file too, so before we begin to read from the file let us close the file first with the close statement.
Code:
Sub Example() Dim Path As String Dim Count As Integer Dim CLine As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Do Until EOF(1) Count = Count + 1 Line Input #1, CLine Loop Close #1 End Sub
Step 9: Since we want to read the file contents and before we begin our loop we need to get a counter to loop throughout the file so first declare a variable as Integer which will help for our loop.
Code:
Sub Example() Dim Path As String Dim Count As Integer Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Close #1 End Sub
Step 10: We want to also read the lines in our text file so we need to declare another variable that will help us to read the contents of the file.
Code:
Sub Example() Dim Path As String Dim Count As Integer Dim CLine As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Close #1 End Sub
Step 11: Now we can start with our loop to read the contents of the file.
Code:
Sub Example() Dim Path As String Dim Count As Integer Dim CLine As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Do Until EOF(1) Loop Close #1 End Sub
Here EOF stands for End of File and 1 is the number of the file which we want to read, as for this example we want the first file to be in the loop.
Step 12: Now let us loop with the end of the file.
Code:
Sub Example() Dim Path As String Dim Count As Integer Dim CLine As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Do Until EOF(1) Count = Count + 1 Loop Close #1 End Sub
Step 13: So now to read the lines in the file we will use the following input code.
Code:
Sub Example() Dim Path As String Dim Count As Integer Dim CLine As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Do Until EOF(1) Count = Count + 1 Line Input #1, CLine Loop Close #1 End Sub
This will take the input from line 1 and put it in our current line.
Step 14: Let us just simply display the contents of the file through a message box function.
Code:
Sub Example() Dim Path As String Dim Count As Integer Dim CLine As String Path = "C:\Users\cba_16\Desktop\text.txt" Open Path For Input As #1 Do Until EOF(1) Count = Count + 1 Line Input #1, CLine MsgBox CLine Loop Close #1 End Sub
Step 15: Run the code by pressing the F5 key or by clicking on the Play Button.
VBA Input – Example #2
In the above example, we simply read the contents of the line from an Input function, but for this example, we will also read the contents of the text file and copy the input to our excel sheet. For this, follow the below steps:
Step 1: We will work in the same module in which we had worked before and declare another sub-function.
Code:
Sub Example2() End Sub
Step 2: Now we know how many variables we need to declare for an input to read from the file.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer End Sub
Step 3: Let us assign the path of our second text file to the variable.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer Path1 = "C:\Users\cba_16\Desktop\text.txt" End Sub
Step 4: We will use the Open statement to open the file and read for the input since for example, this is the only file we are opening so the number of the file still remains 1.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer Path1 = "C:\Users\cba_16\Desktop\text.txt" Open Path1 For Input As #1 End Sub
Step 5: Now we will start with our loop to read the contents of the file.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer Path1 = "C:\Users\cba_16\Desktop\text.txt" Open Path1 For Input As #1 Do Until EOF(1) End Sub
Step 6: Let us loop through the lines and read the file contents with the input statements.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer Path1 = "C:\Users\cba_16\Desktop\text.txt" Open Path1 For Input As #1 Do Until EOF(1) Count1 = Count1 + 1 Line Input #1, CurLine End Sub
Step 7: Now since we want the contents to be written in our excel file.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer Path1 = "C:\Users\cba_16\Desktop\text.txt" Open Path1 For Input As #1 Do Until EOF(1) Count1 = Count1 + 1 Line Input #1, CurLine ThisWorkbook.Sheets("Sheet1").Cells(Count1, 1).Value = CurLine End Sub
Step 8: Now we will end the loop and the open statement.
Code:
Sub Example2() Dim Path1 As String, CurLine As String, Count1 As Integer Path1 = "C:\Users\cba_16\Desktop\text.txt" Open Path1 For Input As #1 Do Until EOF(1) Count1 = Count1 + 1 Line Input #1, CurLine ThisWorkbook.Sheets("Sheet1").Cells(Count1, 1).Value = CurLine Loop Close #1 End Sub
Step 9: When we execute the following code by pressing the F5 key. we can see the results in sheet1.
Conclusion
Input is one of the functions in VBA, these functions are used to read the contents of the file and sometimes they are used with output functions so that we can write something on the file. In developers Input functions, output functions and append functions are used to read the contents and the update is as per the data.
Things to Remember
- Input Statement has to be followed up by the file number.
- When we open a file with the Open statement it is mandatory to close the file to avoid the endless loop.
- Input can be used both for the file as well as the contents of the file.
Recommended Articles
This is a guide to the VBA Input. Here we discuss how to use the Input function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –
