Attribute VB_Name = "Clean_Convert"
Sub CleannConvert()
Attribute CleannConvert.VB_Description = "Macro recorded 8/31/2010 by Jeff Atherton"
Attribute CleannConvert.VB_ProcData.VB_Invoke_Func = " \n14"
'
' Clean_Convert
' Macro recorded 8/31/2010 by Stan Atherton

Dim File(1 To 25) As String
Dim FileOUT(1 To 25) As String
Dim Threethreehundred As String
Dim Threeother As String
Dim Other As String
Dim Dat, Dit As String

'*********** CHANGE ONLY THESE VARIABLES: **************
One = "6:509" 'This selects the rows previous to the data in question for deletion for the DAS300 files
Two = "6:508" 'This selects the rows previous to the data in question for deletion for all other DAS files
Threethreehundred = "6:499"
Threeother = "6:499"
Four = One
Five = One
Dat = "040111"   'Date seen on the report file
Dit = "040111"   'Actual date of data needed
'*******************************************************
' This macro pulls .rpt files from a folder on the desktop of the same name as "Dat"
' converts and deposits Microsoft Excel files into the raw data folder in the external drive

File(1) = "CHARL1_100_" & Dat & ".rpt"
File(2) = "CHARL1_101_" & Dat & ".rpt"
File(3) = "CHARL1_102_" & Dat & ".rpt"
File(4) = "CHARL1_103_" & Dat & ".rpt"
File(5) = "CHARL1_104_" & Dat & ".rpt"
File(6) = "CHARL1_105_" & Dat & ".rpt"
File(7) = "CHARL2_200_" & Dat & ".rpt"
File(8) = "CHARL2_201_" & Dat & ".rpt"
File(9) = "CHARL2_202_" & Dat & ".rpt"
File(10) = "CHARL2_203_" & Dat & ".rpt"
File(11) = "CHARL2_204_" & Dat & ".rpt"
File(12) = "CHARL3_300_" & Dat & ".rpt"
File(13) = "CHARL3_301_" & Dat & ".rpt"
File(14) = "CHARL3_302_" & Dat & ".rpt"
File(15) = "CHARL4_400_" & Dat & ".rpt"
File(16) = "CHARL4_401_" & Dat & ".rpt"
File(17) = "CHARL4_402_" & Dat & ".rpt"
File(18) = "CHARL4_403_" & Dat & ".rpt"
File(19) = "CHARL4_404_" & Dat & ".rpt"
File(20) = "CHARL4_405_" & Dat & ".rpt"
File(21) = "CHARL5_500_" & Dat & ".rpt"
File(22) = "CHARL5_501_" & Dat & ".rpt"
File(23) = "CHARL5_502_" & Dat & ".rpt"
File(24) = "CHARL5_503_" & Dat & ".rpt"
File(25) = "CHARL5_504_" & Dat & ".rpt"
'
FileOUT(1) = "CHARL1_100_" & Dit & ".xls"
FileOUT(2) = "CHARL1_101_" & Dit & ".xls"
FileOUT(3) = "CHARL1_102_" & Dit & ".xls"
FileOUT(4) = "CHARL1_103_" & Dit & ".xls"
FileOUT(5) = "CHARL1_104_" & Dit & ".xls"
FileOUT(6) = "CHARL1_105_" & Dit & ".xls"
FileOUT(7) = "CHARL2_200_" & Dit & ".xls"
FileOUT(8) = "CHARL2_201_" & Dit & ".xls"
FileOUT(9) = "CHARL2_202_" & Dit & ".xls"
FileOUT(10) = "CHARL2_203_" & Dit & ".xls"
FileOUT(11) = "CHARL2_204_" & Dit & ".xls"
FileOUT(12) = "CHARL3_300_" & Dit & ".xls"
FileOUT(13) = "CHARL3_301_" & Dit & ".xls"
FileOUT(14) = "CHARL3_302_" & Dit & ".xls"
FileOUT(15) = "CHARL4_400_" & Dit & ".xls"
FileOUT(16) = "CHARL4_401_" & Dit & ".xls"
FileOUT(17) = "CHARL4_402_" & Dit & ".xls"
FileOUT(18) = "CHARL4_403_" & Dit & ".xls"
FileOUT(19) = "CHARL4_404_" & Dit & ".xls"
FileOUT(20) = "CHARL4_405_" & Dit & ".xls"
FileOUT(21) = "CHARL5_500_" & Dit & ".xls"
FileOUT(22) = "CHARL5_501_" & Dit & ".xls"
FileOUT(23) = "CHARL5_502_" & Dit & ".xls"
FileOUT(24) = "CHARL5_503_" & Dit & ".xls"
FileOUT(25) = "CHARL5_504_" & Dit & ".xls"
'******* For 100-105
   For i = 1 To 6
    Workbooks.OpenText Filename:= _
        "C:\Users\Owner\Desktop\" & Dat & "\" & File(i), _
        Origin:=437, StartRow:=1, 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)), TrailingMinusNumbers:=True
   
    Windows(File(i)).Activate
    Rows(One).Select
    Selection.Delete Shift:=xlUp
    Rows("174:1000").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "F:\SAAtherton\Charleston_Field_Tests\Raw_Data\CHARL1\" & FileOUT(i), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    Next i
'****** For 200-204
    For i = 7 To 11
    Workbooks.OpenText Filename:= _
        "C:\Users\Owner\Desktop\" & Dat & "\" & File(i), _
        Origin:=437, StartRow:=1, 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)), TrailingMinusNumbers:=True
    Windows(File(i)).Activate
    Rows(Two).Select
    Selection.Delete Shift:=xlUp
    Rows("174:1000").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "F:\SAAtherton\Charleston_Field_Tests\Raw_Data\CHARL2\" & FileOUT(i), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    Next i
'******** For 300-302
For i = 12 To 12
    Workbooks.OpenText Filename:= _
        "C:\Users\Owner\Desktop\" & Dat & "\" & File(i), _
        Origin:=437, StartRow:=1, 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)), TrailingMinusNumbers:=True
    Windows(File(i)).Activate
    Rows(Threethreehundred).Select
    Selection.Delete Shift:=xlUp
    Rows("174:1000").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "F:\SAAtherton\Charleston_Field_Tests\Raw_Data\CHARL3\" & FileOUT(i), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    Next i
    ''''
    For i = 13 To 14
    Workbooks.OpenText Filename:= _
        "C:\Users\Owner\Desktop\" & Dat & "\" & File(i), _
        Origin:=437, StartRow:=1, 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)), TrailingMinusNumbers:=True
   
    Windows(File(i)).Activate
    Rows(Threeother).Select
    Selection.Delete Shift:=xlUp
    Rows("174:1000").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "F:\SAAtherton\Charleston_Field_Tests\Raw_Data\CHARL3\" & FileOUT(i), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    Next i
'********For 400-405
   For i = 15 To 20
    Workbooks.OpenText Filename:= _
        "C:\Users\Owner\Desktop\" & Dat & "\" & File(i), _
        Origin:=437, StartRow:=1, 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)), TrailingMinusNumbers:=True
    Windows(File(i)).Activate
    Rows(Four).Select
    Selection.Delete Shift:=xlUp
    Rows("174:1000").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "F:\SAAtherton\Charleston_Field_Tests\Raw_Data\CHARL4\" & FileOUT(i), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    Next i
'******* For 500-504
   For i = 21 To 25
    Workbooks.OpenText Filename:= _
        "C:\Users\Owner\Desktop\" & Dat & "\" & File(i), _
        Origin:=437, StartRow:=1, 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)), TrailingMinusNumbers:=True
    Windows(File(i)).Activate
    Rows(Five).Select
    Selection.Delete Shift:=xlUp
    Rows("174:1000").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "F:\SAAtherton\Charleston_Field_Tests\Raw_Data\CHARL5\" & FileOUT(i), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    ActiveWindow.Close
    Next i
End Sub