Import or copy text file data to excel sheet in VBA Excel

vba excel text to excel data copy

Objective

To import text file data in excel worksheet using VBA Excel.

Approach

Here we have a txt file, which is placed at a path and we have imported the data from txt file to macro file.In the below code we have used the Open statement which creates the buffer to perform the I/O operation on the file.

FreeFile statement returns a number, which refers to a file. For example, if we have not opened any file then FreeFile will return 1 as result,which means any file which will be opened , will be treated as 1st file by VBA application. Once we open a file, then if we again check the value of FreeFile, it will be 2. It is so because VBA application will refer it as 2nd file, since 1st file is already opened.

Here Line Input statement reads the data from one particular line of input txt file and stores the value in variable LineData as shown in the code below. We shall store the value of variable LineData in column A of opened worksheet.

In the code below, we loop through each line of txt file, store the entire text of a line in variable “LineData”, then store the value of variable “LineData” in column A.We shall follow the same pattern for each line in input text file, and then save the output in separate row in excel worksheet.We can see the same in the image below, how data goes from text file to excel worksheet.

Here I have imported the data from text file to macro file itself.

Code

Sub ReadTextFileDataInExcel()

Dim MyInputFile As String
Dim TempFileNum As Integer
Dim LineData As String
MyInputFile = "C:\Users\yourusername\Desktop\macro\New\test1.txt"

'Store the first file number in TempFileNum
TempFileNum = FreeFile
    
RowNumber = 1
 
Open MyInputFile For Input As #TempFileNum

Do While Not EOF(TempFileNum)

'Read data from each line of text file and store it in variable LineData
Line Input #TempFileNum, LineData

'Storing the text file values in column A
Range("A" & RowNumber).Value = LineData

RowNumber = RowNumber + 1
Loop

Close #TempFileNum

End Sub


 

In the image below we can see that we had 3 lines in the input text file, we have used this file in the code.

 

vba excel text to excel data copy

Once we execute the VBA code, data is imported in the excel worksheet as shown below.We can see in the image below that we had 3 lines in the source text file and all of these 3 lines are imported in excel worksheet.

 

vba excel text to excel data copy

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/line-inputstatement
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/open-statement
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function