How can I write a macro to import multiple text file iinto the same excel worksheet

hello,

I'm new to VBA macro!!please help me!!

how can i import a multiple text file(more than 4 files) from a local dir(like D:/Test) into same excel worksheet?

Also i need to save the imported worksheet in new name in the same dir(D:/Test) (not to save in the macro containing worksheet).


write now i have used the below code but i m getting all text files in single sheet. i want to get each text in different sheets.

Sub Read_Text_Files()
Dim sPath As String, sLine As String
Dim oPath As Object, oFile As Object, oFSO As Object
Dim r As Long
'
'Files location
sPath = "D:\Test\"
'
r = 1
Set oFSO = CreateObject( _
"Scripting.FileSystemObject")
Set oPath = oFSO.GetFolder(sPath)
Application.ScreenUpdating = False
For Each oFile In oPath.Files
If LCase(Right(oFile.Name, 4)) = ".txt" Then
'
Open oFile For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, sLine ' Read data
If Left(sLine, 1) = "=" Then sLine = "'" & sLine 
sLine = Replace(sLine, Chr(2), "") 
sLine = Replace(sLine, Chr(3), "")
sLine = Replace(sLine, Chr(10), "")
Range("A" & r).Formula = sLine 
r = r + 1
Loop
Close #1 ' Close file.
'
End If
Next oFile
'
 'Text to Columns
 Range("A1", Range("A" & Cells.Rows.Count).End(xlUp)).Select
 Selection.TextToColumns DataType:=xlDelimited, _
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
 Semicolon:=False, Comma:=False, Space:=True, Other:=False
Application.ScreenUpdating = True
End Sub

thanks in advance,!!