Macro to import multiple text files into one excel (2007) sheet

Hi,

I've spend hours looking for a relatively simple macro but none did work the way I needed it to work and I would appreciate any help here.

I have a very high number of txt files I would like to import into an excel sheet. The txt file all have the same header. The data is tab delimited. All the text file should be imported into one single worksheet. If possible have the header on top and then all the rows containing the data.

I'm using excel 2007.

Thanks in advance.
T.

Hi T

What you're asking for is relatively simple. I have a couple of questions:

Can you upload a couple of the txt files? Press the Go Advanced button at the bottom of the screen and then hit the Manage Attachments button.

Do you want to import all the txt files that are saved in a folder, or select each file individually? Is the folder in a set location, or do you want to browse to it each time?

Do you want to append each time you run, or replace the entire worksheet with the new data?

With the above, we should be able to knock something out quite quickly.

Cheers, Rob.

Hi Rob,

here are the answers to your questions:

Do you want to import all the txt files that are saved in a folder, or select each file individually? Select each file individually.

Is the folder in a set location, or do you want to browse to it each time? Browse to it each time.

Do you want to append each time you run, or replace the entire worksheet with the new data? append each time.

Let me know if you have any additional questions.

Thanks for your help.
T.

Hi

Here is the macro.

Please Login or Register  to view this content.

It opens a text file and copies the data in to the active worksheet. It then closes the text file.

Hope this helps.

Cheers, Rob.

Here's a different method.

Please Login or Register  to view this content.

Thanks Rob. Sorry I misunderstood your question. "Do you want to import all the txt files that are saved in a folder, or select each file individually?" I don't want to have to import each file individually. I thought it would be possible to pick a set of files instead of the entire folder but it is not possible with the macro that you were kind enough to create.

I know your time is precious but would it be possible do have a macro that allows to pick multiple txt files in one procedure? Alternatively a macro that imports the entire folder would be fine too, as I would just have to put all txt files I want to import into one same folder.

Thanks jindon for your help. Is there anything I need to change in the code in order to link to the folder or the file? All I'm getting is this:

ÿþ1
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7

Originally Posted by schthi3

Thanks jindon for your help. Is there anything I need to change in the code in order to link to the folder or the file? All I'm getting is this:

ÿþ1
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7
1 00FFFFF7

Problem fixed! The worksheet needs to be in the same folder as the txt files. Thanks a lot guys.

Hi

The attached version uses a userform to provide a front end, and the ability to select some files and not others.

The macro GetFiles kicks it all off.

Cheers, Rob.

Originally Posted by rscsmith

Hi

The attached version uses a userform to provide a front end, and the ability to select some files and not others.

The macro GetFiles kicks it all off.

Cheers, Rob.

Can you please help when I tried the Macro, I got following error message:

Run-time error '1004':
'abc.txt could not be found. Check the spelling of the file name, and verify that the file location is correct.

I realize it is a kind of "low tech" solution, but Word (at least 2010 version) will allow you to import multiple text files. Use Insert-Object-tect from file... command, and then choose your directory, select whatever files you want, and click Insert. You get all you files appended one after another, after which you are free to copy-paste it into an Excel workbook.

Hope I haven't violated any rules by posting a work-around solution where a proper one has been already posted.

Originally Posted by schthi3

Hi,

I've spend hours looking for a relatively simple macro but none did work the way I needed it to work and I would appreciate any help here.

I have a very high number of txt files I would like to import into an excel sheet. The txt file all have the same header. The data is tab delimited. All the text file should be imported into one single worksheet. If possible have the header on top and then all the rows containing the data.

I'm using excel 2007.

Thanks in advance.
T.