function for importing multiple .txt files?

**willie86005** · 04-12-2012, 01:30 PM

Hello, this is my first post. I did first search the forum for help on my question, but was unable to find the information I am looking for.

I have a folder which contains hundreds of .txt files. I would like the contents of each .txt file to be inserted into one cell. Each row will also have the corresponding pathway to each .txt file.

Like this:

---------A------------------B-----------
1|C:\pathway\1.txt | contents of 1.txt |
2|C:\pathway\2.txt | contents of 2.txt |
3|C:\pathway\3.txt | contents of 3.txt |
4|C:\pathway\4.txt | contents of 4.txt |

I can easily make each pathway a hyperlink using the =HYPERLINK(cell) function for column A. However, I'm not quite sure how to grab the contents of each file and import it into one cell of column B. Basically, I want to take the file listed at A1 and copy all of its contents in cell B1, and so on for each row. Is there a function which would allow me to do this?

**mrice** · 04-12-2012, 02:22 PM

Try this macro

Please Login or Register  to view this content.

You will need to adjust the value for the MyPath variable.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**willie86005** · 04-12-2012, 03:45 PM

I tested this out and it works great. I tagged the macro to a command button and it pulled all the info I need. I am making a searchable Access database and want all the .txt info searchable, so need it all in a table. This macro helps a ton.

Thanks!!