MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Importing Text Files

Posted by Jimmy Rizos on July 16, 2001 6:48 AM

I have a set of text files (~200) that I would like to
import to a workbook, one per worksheet. Is there a
straightforward way to do this?

Posted by Damon Ostrander on July 16, 2001 8:30 AM

Hi Jimmy,

Yes, this is straightforward. You can use the VBA Dir function (check the VBA helps) to, for example, return the names of each of the .txt files in a given directory one at a time. By using this in a loop that reads a file name using Dir, then opens the file in Excel, you can automatically import all the files. Since Excel will create a new workbook for each imported textfile, you will have to use the Move method to move each new sheet to the ThisWorkbook workbook (the workbook containing the VBA code) within the loop.

If this is not sufficient explanation, feel free to follow up.

Happy computing.