Ok, I have Tried To amend the Code as per your requirements.
I have NOT Tested It, but it Should Work. I will Test I ASAP, when I get to a Windows Machine.
Maby I will load a VM on my ubuntu.

The Result should be like this:
____________________________________________________________________
|___A______________________________________B________________________
|Testfile.txt|Sample TextSample TextSample TextSample TextSample TextSample Text
____________________________________________________________________

Code:

''Require that all variables be declared
Option Explicit
''======================================================
'' Program: ParseText
'' Desc: Reads a text file into a variable then
'' writes it into a row, n chars at a time
'' Called by: user
'' Call:
'' Arguments:
'' Comments: Written quickly. No error-checking.
'' Changes----------------------------------------------
'' Date Programmer Change
'' 6/14/06 Charley Kyd Written
''======================================================
Sub ParseText()
''###C0NAN### - New
Dim MyFileSystemObject as Object
Dim MyFolderObject As Object
Dim MyFileObject As Object
Dim MyFileCollection As Object
Dim FolderContainingRawFiles as String
''###C0NAN### - New
Dim sText As String, sFile As String
''Get the full path to the source file
'' With ThisWorkbook
'' sFile = .Names("SourcePath").RefersToRange
'' If Left(sFile, 1) <> "\" Then sFile = sFile & "\"
'' sFile = sFile & .Names("SourceFile").RefersToRange
'' End With
''###C0NAN###
FolderContainingRawFiles = "C:\FolderWithFiles"
Set MyFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set MyFolderObject = MyFileSystemObject.GetFolder(FolderContainingRawFiles)
Set MyFileCollection = MyFolderObject.Files
For Each MyFileObject In MyFileCollection
sFile = MyFileObject.Name
sText = GetText(sFile)
next
''###C0NAN###
''###C0NAN### - Moved into above For Loop
''Get the full text string from the text file
''sText = GetText(sFile)
''###C0NAN### - Moved into above For Loop
''Remove all nonprintable characters from the text
''Comment out if the characters are wanted
sText = Excel.WorksheetFunction.Clean(sText)
''Write to the workbook
WriteToSheet sText,sFile
End Sub
''======================================================
'' Program: GetText
'' Desc: Read a text file into a string and then
'' return the string
'' Called by: ParseText
'' Call: GetText(sFile)
'' Arguments: sFile--The full path to the text file
'' Comments:
'' Changes----------------------------------------------
'' Date Programmer Change
'' 6/14/06 Charley Kyd Written
''======================================================
Function GetText(sFile As String) As String
Dim nSourceFile As Integer, sText As String
''Close any open text files
Close
''Get the number of the next free text file
nSourceFile = FreeFile
''Write the entire file to sText
Open sFile For Input As #nSourceFile
sText = Input$(LOF(1), 1)
Close
GetText = sText
End Function
''======================================================
'' Program: WriteToSheet
'' Desc: Writes a text string to one row of a
'' worksheet, n characters per column
'' Called by: ParseText
'' Call: WriteToSheet sText
'' Arguments: sText--String with text from text file
'' Comments: This routine will throw an error if the
'' text is too long to fit within 256 columns
'' Changes----------------------------------------------
'' Date Programmer Change
'' 6/14/06 Charley Kyd Written
''======================================================
''###C0NAN### - I have Changed This To Send In the File Name as well
''Sub WriteToSheet(sText As String)
''###C0NAN### - I have Changed This To Send In the File Name as well
Sub WriteToSheet(sText As String,sFile as String)
Dim sTgtSheet As String, nTgtRow As Integer
Dim nColCount As Integer, sChunk As String
Dim nIncrement As Integer, rngRef As Range
''Get the controlling variables
With ThisWorkbook
sTgtSheet = .Names("TargetSheet").RefersToRange
''###C0NAN### - If you use this, It will overrite the rame row for each file
''nTgtRow = .Names("TargetRow").RefersToRange
''###C0NAN### - If you use this, It will overrite the rame row for each file
''###C0NAN### - Now this Will Automaticaly increment to the Next Unused Row.
nTgtRow = Worksheets(sTgtSheet).UsedRange.Rows.Count + 1
''###C0NAN### - Now this Will Automaticaly increment to the Next Unused Row.
''###C0NAN### - This is here to limit the amount of characters per cell...You can Skip this if you Want
''nIncrement = .Names("Increment").RefersToRange
''###C0NAN### - This is here to limit the amount of characters per cell...You can Skip this if you Want
''###C0NAN### - Like this - OR Later in Code
nIncrement = len(sText)
''###C0NAN### - Like this - OR Later in Code
Set rngRef = Worksheets(sTgtSheet).Cells(nTgtRow, 1)
End With
''Erase any previous entries
rngRef.EntireRow.ClearContents
''Initialize the column counter
nColCount = 0
''###C0NAN### - I am going to skip this and give you another sipmler option
''Loop thru the string, grabbing text of specified
''length, writing the text to the spreadsheet
''Do
''Increment the column count
'' nColCount = nColCount + 1
''Grab the current chunk of text
'' sChunk = Mid$(sText, 1 + (nColCount - 1) * nIncrement, nIncrement)
''Write it to the spreadsheet
''###C0NAN### - Dont Worry About this row refering to Row Index= 1, as this is directly related to the Range.
'' rngRef.Cells(1, nColCount) = sChunk
''###C0NAN### - Dont Worry About this row refering to Row Index= 1, as this is directly related to the Range.
''Stop after writing a partial chunk
''Loop Until Len(sChunk) < nIncrement
''###C0NAN### - I am going to skip this and give you another sipmler option
''###C0NAN### - Here is A simpler Option
rngRef.Cells(1, 1) = sFile
rngRef.Cells(1, 2) = sText
''###C0NAN### - Here is A simpler Option
End Sub

Hi, another handy home hint if you want to get the names of all the files in a directory into a text file so you can copy them into excel etc is to just drop to the command line in what ever folder you want to list all the files in and then enter

Code:

dir *.* > mytextfile.txt

Where mytextfile.txt is the name where you want all the files stored.

You can just get all the text documents by changing the extension like

I have Rewritten this code on a windows machine where I was able to test it.
I have also added a "Recursive" feature.

You can either copy the "XLS" file to the Folder containing the loose "TXT"
files, Or you could copy the "XLS" file to a TOP level Folder. I will then Recursively scan all the folders Downwards and import ALL txt files.

Hi , may i know what version of excel did you use ?
I am using excel 2007 , it does show up the Dialog box , but when I click import nothing happens , maybe because it is doing that because its opening in "Compatibilty Mode" but I have enabled Macros .

I checked the VB code , by going to the developer mode and shows the form but no code

1. Open the Module1
2. Go to the Function ParseFolder(MyFolderObject ......)
3. Go to Line With: "For Each MyFileObject in MyFilesCollection.
4. Make Sure The Curer is on that line...
5. Press "F9" Key
6. A Brown line should appear.
7. Press "F5" key.
8. One Of 4 thing could Happen
8.1 Either The Code Will Execute And The Brown Line Will Become Yellow(Line Break Worked)
8.2 A Form Will Appear asking which Macro to Run (Choose ParseFile, 8.1 should Now Happen)
8.3 You will get a message telling you that you are NOT allowed to run macros(Security again)
8.4 Nothing Happens....(Have No Answer for you here, Makes NO Sense)

@Conan it works like an charm now
Here is the Working copy of txt to csv application
Instructions:
1) extract the files
2) paste the articles .txt files in the extracted folder , if you need categories you can place the text files inside the folders with category name
3) Open TxtImport.xls and click on sheet 1 then sheet 2
4) Dialog box will pop up hit "Import Data" and see the magic
all the files within the root folder of the script and other folders will be imported to the sheeet save it as .csv and you are good to go .

@Conan it works like an charm now
Here is the Working copy of txt to csv application
Instructions:
1) extract the files
2) paste the articles .txt files in the extracted folder , if you need categories you can place the text files inside the folders with category name
3) Open TxtImport.xls and click on sheet 1 then sheet 2
4) Dialog box will pop up hit "Import Data" and see the magic
all the files within the root folder of the script and other folders will be imported to the sheeet save it as .csv and you are good to go .

Note that adblockers might block our captcha, and other functionality on BHW so if you don't see the captcha or see reduced functionality please disable adblockers to ensure full functionality, note we only allow relevant management verified ads on BHW.