Macro to import entire text file into single cell

2 -- Text File2 -- Text File2 ContentHi

I want to run a macro to Import Entire Text File in Single Cell

I have some Text Files in a Directory
Eg: C:\Records\

Each Sub Folder will have 1 Text File

Eg: C:\Records\Folder1\Text File1.txt
C:\Records\Folder2\Text File2.txt
C:\Records\Folder3\Text File3.txt

I want to import Each Text File into One Single Cell in a sheet

Like
Row -- Column A -- Column B
1 -- Text File1 -- Text File1 Content
2 -- Text File2 -- Text File2 Content
3 -- Text File3 -- Text File3 Content
4
5

Is it possible to this through VBA. I have posted this question in many forums but no where I got response. Please at-least tell me if it is impossible or no way to do this

Please, please help me....

Thanks a million in advance

Nick's picture

does each text file contain 1

does each text file contain 1 line of data ?

Thank you, Got it

Thanks for the reply

Got some sort of solution after a long wait

Dim iRow As Long
Dim Pathf, Path1 As String
Dim subf As Boolean
Dim Fdate, Tdate As Date
Dim intC As Integer
Dim Folder As Folder
Dim f As file
Dim fs As New FileSystemObject
Sub ListFiles()
Application.ScreenUpdating = False

iRow = 3
Path1 = "D:\Documents and Settings\Desktop\Tool test\"
Pathf = fs.GetFolder(Path1)
subf = True
Call ListMyFiles(Pathf, subf)

End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Dim FileName As String
Dim FileNum As Integer
Dim Data As String
Dim Txt As String

Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next

For Each myfile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value = myfile.Name
iCol = iCol + 1

FileName = myfile
FileNum = FreeFile
Open FileName For Input As #FileNum
Do While Not EOF(FileNum)
Line Input #FileNum, Data
Txt = Txt & Join(Split(Data, vbTab), " ") & " "
Loop
Cells(iRow, iCol).Value = Trim(Txt)
Txt = ""
Close #FileNum
iRow = iRow + 1
Next

If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub

Nick's picture

interesting.. most of that

interesting..
most of that code originates from this site !
... I know that because I wrote it.

Specifically this:
http://excelexperts.com/VBA-List-Files-In-Folder

I hope they've kindly mentioned their source.

Nick