R
Ray
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.
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.