Looping Through Txt Files in Folder and Pasting Contents to Worksheet

[bruppert]

Hello,

I am new to VBA and am wondering how I can loop through a folder containing multiple .txt files and paste the data into worksheet cells. These are tab delimited files.

Some of the requirements are..

1. I want to start reading each .txt file from the second line. The .txt file has headers that I don't want in the worksheet.

2. Each row of the .txt file needs to be placed into its own cell, going down a single column. For example, if the .txt file has 5 rows, the worksheet will be filled (A1:A5)

3. Each file in the folder will be separated by a blank row.


Any help would be really awesome! Cheers.

[bruppert]

Clarification, the files can be separated in a different way. Doesn't need to be a single blank row.

[Leith Ross]

Hello Bruppert,

This macro let's you select the folder with the text files to be imported. The files are copied from line 2 to the end of the file onto worksheet "Sheet1", starting with cell "A1". After the file is copied, the next row reads "(End Of File")". Blank rows are not reliable markers as the file itself could have blank rows. Only files with ".txt" extensions are opened. The macro can be changes to open more than one extension e.g. "*.txt;*.csv" for text files and Comma Separated Values.

Please Login or Register  to view this content.

[bruppert]

This is great! Thank you Leith.

If I wanted the files to be stacked on top of each other with no separation between them in the worksheet, how would I go about this?

[Leith Ross]

Hello bruppert,

Change this line from...

Please Login or Register  to view this content.

Change this line to...

Please Login or Register  to view this content.

To remove the blank row separating the files after (End Of File)

[bruppert]

Perfect. Thank you.

[Leith Ross]

Hello bruppert,

Glad I could help. I am going to mark this post solved, unless you have some other questions.

[bruppert]

Last question,

How would I update the code so that there is no separation at all between the .txt files in the worksheet?

[bruppert]

Never mind, found out how to do this. I first resized the range to (n,1) and then offset the range to (k-2,0). This post is solved!