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

S

samuel.chausse

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
 
B

Bernie Deitrick

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to run on all files in a folder but exclude sheets with PivotTable, Pivot Charts and Charts 16
FileSearch parameters locked 4
VBA to open a PDF, print, then close - Stumped 2
Help on this code please 1
List all files in a sub directory 4
VBA code working in excel2003 but not in excel2007 2
Print all Sheet2 of all Workbooks in Folder 4
read all the files after choosing a directory 5

Top