Load text files to ONE sheet

[surakexcess]

Dear all,

I have found the following code for importing different text files into different worksheets (one sheet for each txt file)
This works great, but i'd like that all data is on one sheet (importing the data of the next txt file in the row following on the latest filled row)

Now i have this code:

Sub Combinetxt()
Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
'Dim sDelimiter As String <<- edited
On Error GoTo ErrHandler
Application.ScreenUpdating = False
'sDelimiter = "vbSpace" '<- edited
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:=True, _
Other:=False
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:=True, _
Other:=False
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



In the end there are many empty rows.
Can i delete them all by using a macro?

Kr,
Steven

[snb]

Why not:


Sub M_snb()
shell "cmd /c copy ""G:\OF\*.txt"" G:\OF\alles.csv",0
workbooks.open G:\OF\alles.csv"
End Sub