importing multiple txt files into one worksheet

[cwsmith87]

Hi there, i Have a task, i have about 650 txt files, containing several lines of information in them, i want the content of each txt file to be in each cell descending, for example txt file content one in a1, txt file two in a2 and so on, i dont want any delimiters, just the whole chunk of text from each file in each cell, the closest i have found so far is the following

Sub CombineTextFiles()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = "|"

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(FileName:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

the way this macro works is great, the only problem is it puts each txt file into a new worksheet everytime, thus ending with 650 worksheets, which is not what i want, I shall reiterate that i want each chunk of text in each cell going down decending, a1,a2,a3 etc, please can someone help me with this macro, it will be greatly appreciated.

 

[Andrew Poulsom]

Look here:

http://www.rondebruin.nl/csv.htm

 

[cwsmith87]

Hi andrew, cheers for this, but its not exactly what im looking for, its asking me to combine all my text files, but once they are combined i dont have anyway of uncombining them, and it goes across not downwards but thank you for you input, can anyone else help?

 

[Andrew Poulsom]

The text files are merged one below the other aren't they?

 

[cwsmith87]

yeah they are in one single text file, then when i go to import into excel, there is no way of seperating them again

 

[Andrew Poulsom]

You said:

i want the content of each txt file to be in each cell descending, for example txt file content one in a1, txt file two in a2 and so on

You didn't mention being able to separate them afterwards. How would you envisage being able to do that?

 

[cwsmith87]

i know thats exactly how i want it txt1 in a1 txt2 in a2, but the coding on that page, asks you to compile txt1 txt2 txt3 etc into one txt file called txt all, and then to import that file, which isnt what i want at all....

 

[Andrew Poulsom]

Sorry, I don't see the distinction there. The result is the same.

 

[alanwh]

I know this is an old post but this is exactly what i needed with one exception.

Is there a line of code i can add to bring in the title of the csv file?

There are some key identifiers in the tiltle of my csv files that are not in the file itself.

Thanks

Alan