Import multiple text files into excel with multiple sheets

[Azarudeen]

Below code imports multiple text file into new excel file but I want the result in the same excel where I'm running macro. Also I would like to call the path(Desktop path like "C:\Desktop") instead of using Get open file path which is used below so that all the text files in the destination will convert in to excel. Can you please assist me on this
<Code>
Sub CombineTextFiles()

'updateby Extendoffice 20151015

Dim xFilesToOpen As Variant

Dim I As Integer

Dim xWb As Workbook

Dim xTempWb As Workbook

Dim xDelimiter As String

Dim xScreen As Boolean

On Error GoTo ErrHandler

xScreen = Application.ScreenUpdating

Application.ScreenUpdating = False

xDelimiter = "|"

xFilesToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Kutools for Excel", , True)

If TypeName(xFilesToOpen) = "Boolean" Then

MsgBox "No files were selected", , "Kutools for Excel"

GoTo ExitHandler

End If

I = 1

Set xTempWb = Workbooks.Open(xFilesToOpen(I))

xTempWb.Sheets(1).Copy

Set xWb = Application.ActiveWorkbook

xTempWb.Close False

xWb.Worksheets(I).Columns("A:A").TextToColumns _

Destination:=Range("A1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, _

ConsecutiveDelimiter:=False, _

Tab:=False, Semicolon:=False, _

Comma:=False, Space:=False, _

Other:=True, OtherChar:="|"

Do While I < UBound(xFilesToOpen)

I = I + 1

Set xTempWb = Workbooks.Open(xFilesToOpen(I))

With xWb

xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)

.Worksheets(I).Columns("A:A").TextToColumns _

Destination:=Range("A1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, _

ConsecutiveDelimiter:=False, _

Tab:=False, Semicolon:=False, _

Comma:=False, Space:=False, _

Other:=True, OtherChar:=xDelimiter

End With

Loop

ExitHandler:

Application.ScreenUpdating = xScreen

Set xWb = Nothing

Set xTempWb = Nothing

Exit Sub

ErrHandler:

MsgBox Err.Description, , "Kutools for Excel"

Resume ExitHandler

End Sub
</Code>

 

[Azarudeen]

I got the below code it works as expected. But Its creating duplicate sheets if I run it again which is affecting other formula. Could you please help me to fix. If I run this macro, it should not create duplicate sheets

<QUOTE>
Sub TxtImporter()Dim f As String, flPath As StringDim i As Long, j As LongDim ws As WorksheetApplication.DisplayAlerts = FalseApplication.ScreenUpdating = FalseflPath = ThisWorkbook.Path & Application.PathSeparatori = ThisWorkbook.Worksheets.Countj = Application.Workbooks.Countf = Dir(flPath & "*.txt")Do Until f = "" Workbooks.OpenText flPath & f, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _ Space:=False, Other:=False, TrailingMinusNumbers:=True Workbooks(j + 1).Worksheets(1).Copy After:=ThisWorkbook.Worksheets(i) ThisWorkbook.Worksheets(i + 1).Name = Left(f, Len(f) - 4) Workbooks(j + 1).Close SaveChanges:=False i = i + 1 f = DirLoopApplication.DisplayAlerts = TrueEnd Sub
</QUOTE>