How do I use a macro to Import mutiple .txt files into excel 2000?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got as far as Importing a single file into a specific location (A:1) But
I need to produce a macro that consults multiple .txt files and appends them
to individual excel lines.

Any help or guidance would be greatly appreciated

Andy
 
Andy,

If all your text files are .txt files, and are in the same folder, then try
the macro below. One big assumption is that the files have data in column A
when imported, and that there aren't any empty rows or columns separating
the data. You may also need to change some of the parameters in the OpenText
command - the best way is to record a macro while opening one of your files.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateTextFiles()
With Application.FileSearch
.NewSearch
'Change this to your directory with the text files
.LookIn = "C:\Excel"
.SearchSubFolders = False 'Change to True if needed
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) Like "*.txt" Then
Workbooks.OpenText Filename:=.FoundFiles(i), Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
Range("A1").CurrentRegion.Copy
Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
ActiveWorkbook.Close
End If
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated
file.xls")
End If
End With
End Sub
 
Back
Top