Joel,
Thanks for the reply. I did try your code but I got an error at
Set NewSht = .Sheets.Add( _
after:=.Sheets(Sheets.Count))



"Joel" wrote:

See if this works.

Sub combine()

FolderName = "D:\Biolum\Survey Data" '\600708\Cast001
ChDrive "D"
ChDir FolderName

Set fs = CreateObject("Scripting.FileSystemObject")
Set Folder = _
fs.GetFolder(FolderName)

If Folder.subfolders.Count 0 Then
For Each Sf In Folder.subfolders
With ThisWorkbook
'Create New Sheet
Set NewSht = .Sheets.Add( _
after:=.Sheets(Sheets.Count))
NewSht.Name = Sf.Name

With NewSht
.Range("B1") = "RecNbr"
.Range("C1") = "Time"
.Range("D1") = "Depth"
.Range("E1") = "BIO cps"
.Range("F1") = "NDx"
.Range("G1") = "RTmp"
.Range("H1") = "CHL"
.Range("I1") = "Cnd"
.Range("J1") = "Trans"
.Range("K1") = "LSS"
.Range("L1") = "Batt"
.Range("M1") = "Lat"
.Range("N1") = "Long"

For Each Myfile In Folder.Files
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

With .QueryTables.Add( _
Connection:="TEXT;" & Myfile.Path, _
Destination:=.Range("A" & NewRow))

.Name = Myfile.Path
.TextFileParseType = xlDelimited
.TextFileTextQualifier = _
xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
Next Myfile
End With
End With
Next Sf

ThisWorkbook.Save
End If

End Sub



"Ray" wrote:

This may be complicated so please bear with me. I have 68 .txt files that I
need to open in excel and delete some header information. Then I need to
save the file as an .xls. I have managed to develop the code to do one file
but I would like to create a Loop of sorts to do the rest without having to
write the code for each file. For one file the code looks like:

ChDir "D:\Biolum\Survey Data\600708\Cast001"
Workbooks.OpenText Filename:= _
"D:\Biolum\Survey Data\600708\Cast001\60070801.txt", Origin:=437,
StartRow _
:=30, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array _
(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8,
1), Array(9, 1), Array( _
10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1)),
TrailingMinusNumbers _
:=True
With ActiveWindow
.Width = 791.25
.Height = 599.25
End With
ActiveWindow.SmallScroll Down:=-18
Rows("1:1").Select
Selection.Insert Shift:=x1Down
Range("B1").Select
ActiveCell.Formula = "RecNbr"
Range("C1").Select
ActiveCell.Formula = "Time"
Range("D1").Select
ActiveCell.Formula = "Depth"
Range("E1").Select
ActiveCell.Formula = "BIO cps"
Range("F1").Select
ActiveCell.Formula = "NDx"
Range("G1").Select
ActiveCell.Formula = "Tmp"
Range("H1").Select
ActiveCell.Formula = "CHL"
Range("I1").Select
ActiveCell.Formula = "Cnd"
Range("J1").Select
ActiveCell.Formula = "Trans"
Range("K1").Select
ActiveCell.Formula = "LSS"
Range("L1").Select
ActiveCell.Formula = "Batt"
Range("M1").Select
ActiveCell.Formula = "Lat"
Range("N1").Select
ActiveCell.Formula = "Long"
ActiveWorkbook.SaveAs Filename:= _
"D:\Biolum\Survey Data\600708\60070801.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close


What will change for each file is the Cast number (Cast001, Cast002, etc)
and the file name (60070801.xls, 60070802.xls, etx).

Any help will be greatly appreciated.

Cheers,
-Ray

And while I'm at it, I plan to combine each file into a master workbook
with each cast on it's own tab. Any help with that would be great as well.