Have you ever wanted a list from a text file to be imported into Excel?

Using VBA you can import data to your Excel file from many types of files. Automating this process with VBA will allow you to quickly pull data avoiding unnecessary manual copy and paste techniques.

Note: This procedure builds on my previous posts using VBA to generate File Lists in Text files. Batch for Obtaining MS Excel File List and VBA To publish a Batch File from MS Excel

Tools: MS Excel and the batch file called filelist.bat from the previous blogs

Please see reference Excel file to follow along—the file has the VBA to write the batch file, to run it, and to the below code to import the text files data.
The Code:

Sub OpenFileList_User_Entry()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Capture Files in directory for processing (can be a network drive)
'
' Imports .txt into Excel on Sheet2 column A
'
' Authored by Alexander Zbiciak 1/14/15
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim Filepath As String
Dim row_number As Integer
Dim Folder As String

'Delete old list from column A

Sheet2.Columns(1).ClearContents

Sheet2.Cells(1, 1) = "File Name"

'Pull in new list, first declare folder and file

Folder = <<Enter your file path here with a closing \>>

'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
'
' Example: Folder = "C:\Users\Alexander zbiciak\Batch Example\"
'
'’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Filepath = Folder & "fileslist.txt"

'Select Output location (Will start at row 2 and be column A)
Sheet2.Cells(2, 1).Select

'Loop each line to a cell in Excel
Open Filepath For Input As #1

row_number = 0

Do Until EOF(1)

Line Input #1, LineFromFile

LineItems = Split(LineFromFile, "")

ActiveCell.Offset(row_number, 0).Value = LineItems(0)
row_number = row_number + 1

Loop

Close #1

'''''''''''''''''''''''''''''''''''''''''''''''''''
'
' If your batch file does not filter out the non Excel files you will need to control for it here
'
' Add in VBA to clean your list
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

How To:

Download the example file. Use this as a template to understand how to import the contents of the .txt. Later you can change where data is written to.

Write the Batch file and save it to the folder you want the file list from

Open the file and the developer tab

File >> Options >> Customize Ribbon >> Developer

Look for Module 1 and find the VBA labelled “Sub OpenFileList_User_Entry()”. The sub is the same code as above. Once you are in the code, you will see the red text that needs to be updated:

Update the file path to the location you will have your files and batch file at.

The VBA code will pull in all files in the directory you declare. Please note that there is the chance you will bring in non-excel files depending on how you wrote your batch file. The batch file should limit to .xls type files, but always check to be sure the command *.xls? in the batch file isn’t bringing in the wrong types of files.