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

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

Hi Stan,

Tom may have gone to bed <g, so perhaps I can offer some assistance.

To change the paste so that it transposes the rows (i.e. the data runs down
the columns rather than across the rows) you need to change the line:

rng1.Copy Destination:=wkbk.Worksheets(1).Range("A1")

To read:

rng1.Copy
wkbk.Worksheets(1).Range("A1").PasteSpecial , , , True

To save them as csv files try changing:

wkbk.SaveAs FileName:=sPath & cell.Row & ".xls"

to:

wkbk.SaveAs FileName:=sPath & cell.Row & ".txt", FileFormat:=xlCSV

to wrap up opening multiple workbooks you might use something like:

Dim sFName As String
sName = Dir("c:\inputdata\*.*")
do while len(sName) 0
Workbooks.open(sname)
Set wkbk = ActiveWorkbook
. . . code as given by Tom, with my amendments
sName = Dir() ' Note, no parameters this time as we want to get the
next file
loop

HTH

Peter Beach

"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

I truly appreciate your help with my problem!

May I please ask one last thing?

I apologize for not mentioning that my input files are
all "tab delimited".

How can I change the code, so that it could work for "tab
delimited" input files?

Thank you all very much for your time


Stan