function for importing multiple .txt files?

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?

Try this macro

Sub Test()
MyPath = "C:\temp\test\"
Close
MyFileName = Dir(MyPath & "*.txt")
Do While MyFileName <> ""
    Counter = Counter + 1
    MyString = ""
    Open MyPath & MyFileName For Input As #1
    Do While Not EOF(1)
        Line Input #1, FileLine
        MyString = MyString & FileLine & Chr$(10)
    Loop
    If MyString <> "" Then MyString = Left(MyString, Len(MyString) - 1)
    Cells(Counter, 1) = MyPath & MyFileName
    Cells(Counter, 2) = MyString
    Close #1
    MyFileName = Dir()
Loop
End Sub

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.

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!!