As written, gets files from c:\Inputdata, writes to c:\data1

Opening a .txt file requires specifying how Excel is to interpret it. You
gave me no information on that. I have assumed the values in the text file
are separated by commas and if there are any text fields, the don't contain
embedded commas.


Sub Buildfiles()
Dim wkbk As Workbook, sh As Worksheet
Dim sPath As String, sName As String
Dim cell As Range, rng As Range, rng1 As Range
sPath = "C:\Data1\"
Set wkbk = Workbooks.Add(xlWBATWorksheet)

With Application.FileSearch
.NewSearch
.LookIn = "c:\inputdata\"
.SearchSubFolders = False
.FileName = "*.txt"
.FileType = msoFileTypeAllFiles
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.OpenText _
FileName:=.FoundFiles(i), _
origin:=xlWindows, _
DataType:=xlDelimited, _
comma:=True
Set wkbk1 = ActiveWorkbook
sName = Left(wkbk1.Name, Len(wkbk1.Name) - 4)
Set sh = wkbk1.Worksheets(1)
Set rng = sh.Range(sh.Cells(1, "C"), _
sh.Cells(Rows.Count, "C").End(xlUp))
For Each cell In rng
Set rng1 = sh.Range(cell, _
sh.Cells(cell.Row, "IV").End(xlToLeft))
rng1.Copy
wkbk.Worksheets(1).Range("A1").PasteSpecial _
Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
Application.DisplayAlerts = False
wkbk.SaveAs FileName:=sPath & cell.Row & "_" & _
sName & ".txt", _
FileFormat:=xlTextMSDOS
Application.DisplayAlerts = True
wkbk.Worksheets(1). _
UsedRange.ClearContents
Next
wkbk1.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If


End With

wkbk.Close SaveChanges:=False
End Sub

--
Regards,
Tom Ogilvy

Stan Bauer wrote in message
...
Hello Mr. Ogilvy

Thank you so much! I hope you get back all of the karma
you generate!

Mr. Ogilvy, may I please ask about two more things?

Your code generates xls files with a row in it. I need
text files with the text displayed as a column [i.e. we
copy a row from the input file and paste it as a column in
the output file].

I tried to simply replace ".xls" by ".txt" in your code,
but it didn't help. May I please ask whether it would be
possible to change the code to do this?

I apologize about bringing up the second thing. I feel a
little as if you agreed to give me the little finger. If
this is not simple, please ignore it.

I have 800 files, each with C columns and R rows, that I
need to do this with. All of these files are in
c:\inputdata\ folder.
Each file is named 1.txt, 2.txt, ... T.txt

Would it be difficult to have the code open each of these
files in turn, run the algorithm and save row w from file
c:\inputdata\q.txt as c:\data\w_q.txt - a text file with
one column in it?

Thank you, Mr. Ogilvy

Warm Wishes


Stan