EDUCBA

Home VBA VBA Resources VBA Message Functions VBA Input
 

VBA Input

Madhuri Thakur
Article byMadhuri Thakur

VBA Input

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: 

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

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

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.

Insert Module

Step 2: Once the new module is inserted we can start with our subprocedure for this example.

Code:

Sub Example()

End Sub

VBA Input Example1-1

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

VBA Input Example1-2

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.

VBA Input Example - Text properties

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

VBA Input Example1-3

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

VBA Input Example1-4

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

VBA Input Example1-77

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

Close Statement Example - close statement

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

VBA Input Example - Integer

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

VBA Input Example - File contents

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

VBA Input Example - contents

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

End of File Example1-8

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

VBA Input Example1-9

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

VBA Input Example1-10

Step 15: Run the code by pressing the F5 key or by clicking on the Play Button.

VBA Input Example1-11

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

Sub-Function Example 2-1

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

VBA Input Example 2-2

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

Text File Example 2-3

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

VBA Input Example 2-4

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

Loop Example 2-5

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

VBA Input Example 2-6

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

VBA Input Example 2-8

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

Open Sstatement Example2-9

Step 9: When we execute the following code by pressing the F5 key. we can see the results in sheet1.

sheet1 Example2-11

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 –

  1. VBA SendKeys
  2. VBA Name Worksheet
  3. VBA On Error Goto
  4. VBA Msgbox Yes/No