Importing all the .txt files of a folder in a same WB but in different WSheets

Hi there,

I have a question very closed from closed subject (IMPORTING 100 TEXT
FILES INTO EXCEL AT ONCE, WITH LABELLING): How to import all the .txt
files from a folder, and import them in seperate worksheets? I mean : I
have several txt files and I would like to have them imported, each of
them, in a separate worksheet

--sorry I am not English mother tongue-

Here is the VBa code I generated (and modified) :


Sub Macro1()
'
' Macro1 Macro
' Macro enregistr=E9e le 27/06/2006 par samuel.chausse

With Application.FileSearch
..NewSearch
..LookIn =3D "U:\Services\"
..SearchSubFolders =3D True
..Filename =3D "*.txt"
..MatchTextExactly =3D True
..FileType =3D msoFileTypeAllFiles
End With

With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i =3D 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=3D"TEXT;" &
..FoundFiles(i), Destination:=3DRange("A1"))
.FieldNames =3D True
.RowNumbers =3D False
.FillAdjacentFormulas =3D False
.PreserveFormatting =3D True
.RefreshOnFileOpen =3D False
.RefreshStyle =3D xlInsertDeleteCells
.SavePassword =3D False
.SaveData =3D True
.AdjustColumnWidth =3D True
.RefreshPeriod =3D 0
.TextFilePromptOnRefresh =3D False
.TextFilePlatform =3D 1252
.TextFileStartRow =3D 1
.TextFileParseType =3D xlDelimited
.TextFileTextQualifier =3D xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter =3D False
.TextFileTabDelimiter =3D True
.TextFileSemicolonDelimiter =3D False
.TextFileCommaDelimiter =3D False
.TextFileSpaceDelimiter =3D False
.TextFileColumnDataTypes =3D Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers =3D True
'.Refresh BackgroundQuery:=3DFalse
End With
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub


Obviously there is a mistake because (even if no error message
pops-up) there is nothing into the worksheets.

Is there someone to correct my code or to give me a hand. I need this
program in order to be able to deal with all these datas. Thank you
very much

Samuel - France

Samuel,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application.FileSearch
..NewSearch
..LookIn = "U:\Services\"
..SearchSubFolders = True
..Filename = "*.txt"
..FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
Set mysht = Worksheets.Add
Workbooks.OpenText Filename:=.FoundFiles(i), Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Cells.Copy mysht.Cells
ActiveWorkbook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls")
End If
End With
End Sub


<[email protected]> wrote in message
news:[email protected]...
Hi there,

I have a question very closed from closed subject (IMPORTING 100 TEXT
FILES INTO EXCEL AT ONCE, WITH LABELLING): How to import all the .txt
files from a folder, and import them in seperate worksheets? I mean : I
have several txt files and I would like to have them imported, each of
them, in a separate worksheet

--sorry I am not English mother tongue-

Here is the VBa code I generated (and modified) :


Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 27/06/2006 par samuel.chausse

With Application.FileSearch
..NewSearch
..LookIn = "U:\Services\"
..SearchSubFolders = True
..Filename = "*.txt"
..MatchTextExactly = True
..FileType = msoFileTypeAllFiles
End With

With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" &
..FoundFiles(i), Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
'.Refresh BackgroundQuery:=False
End With
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub


Obviously there is a mistake because (even if no error message
pops-up) there is nothing into the worksheets.

Is there someone to correct my code or to give me a hand. I need this
program in order to be able to deal with all these datas. Thank you
very much

Samuel - France

Just one word : Brillant !

Thank you very much ! :-)

Samuel

Just one word : Brillant !

Thank you very much ! :-)

Samuel

Samuel,

Glad you liked it ;-)

Bernie
MS Excel MVP

> Just one word : Brillant !