Sub AFS_Data_Charts()

Dim TimeRange As Range
Dim StartRow, EndRow As Integer
Dim DataSheetName As String


' Open the file dialog box and let the user select which file to import
Call Import_AFS_Data
DataSheetName = ActiveSheet.Name

' Delete the Version info row
Rows(1).Delete

' Freeze first row for scrolling
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True

' Determined what data columns were imported from the file
' Tracks column number for each data type
' Sets column number to 0 if the data type isn't included

Dim HeaderRange As Range
Set HeaderRange = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
With HeaderRange
    'DATE
    Set Header = .Find("DATE", LookIn:=xlValues)
    If Header Is Nothing Then DateCol = 0 Else DateCol = Header.Column
    'TIME
    Set Header = .Find("TIME", LookIn:=xlValues)
    If Header Is Nothing Then TimeCol = 0 Else TimeCol = Header.Column
    'ALTITUDE
    Set Header = .Find("ALTITUDE", LookIn:=xlValues)
    If Header Is Nothing Then AltitudeCol = 0 Else AltitudeCol = Header.Column
    'AOA
    Set Header = .Find("AOA", LookIn:=xlValues)
    If Header Is Nothing Then AOACol = 0 Else AOACol = Header.Column
    'AIRSPEED
    Set Header = .Find("AIRSPEED", LookIn:=xlValues)
    If Header Is Nothing Then AirspeedCol = 0 Else AirspeedCol = Header.Column
    'BATVOLTS
    Set Header = .Find("BATVOLTS", LookIn:=xlValues)
    If Header Is Nothing Then BatVoltsCol = 0 Else BatVoltsCol = Header.Column
    'VOLTS
    Set Header = .Find("VOLTS", LookIn:=xlValues)
    If Header Is Nothing Then VoltsCol = 0 Else VoltsCol = Header.Column
    'OAT
    Set Header = .Find("OAT", LookIn:=xlValues)
    If Header Is Nothing Then OATCol = 0 Else OATCol = Header.Column
    'BACKVOLTS
    Set Header = .Find("BACKVOLTS", LookIn:=xlValues)
    If Header Is Nothing Then BackVoltsCol = 0 Else BackVoltsCol = Header.Column
    'VERTSPEED
    Set Header = .Find("VERTSPEED", LookIn:=xlValues)
    If Header Is Nothing Then VertSpeedCol = 0 Else VertSpeedCol = Header.Column
    'FUEL_COMP
    Set Header = .Find("FUEL_COMP", LookIn:=xlValues)
    If Header Is Nothing Then FuelCompCol = 0 Else FuelCompCol = Header.Column
    'RPM
    Set Header = .Find("RPM", LookIn:=xlValues)
    If Header Is Nothing Then RPMCol = 0 Else RPMCol = Header.Column
    'MANIFOLD
    Set Header = .Find("MANIFOLD", LookIn:=xlValues)
    If Header Is Nothing Then ManifoldCol = 0 Else ManifoldCol = Header.Column
    'FUEL_PSI
    Set Header = .Find("FUEL_PSI", LookIn:=xlValues)
    If Header Is Nothing Then FuelPSICol = 0 Else FuelPSICol = Header.Column
    'FUEL_FLOW
    Set Header = .Find("FUEL_FLOW", LookIn:=xlValues)
    If Header Is Nothing Then FuelFlowCol = 0 Else FuelFlowCol = Header.Column
    'AMPS_SHUNT
    Set Header = .Find("AMPS_SHUNT", LookIn:=xlValues)
    If Header Is Nothing Then AmpsShuntCol = 0 Else AmpsShuntCol = Header.Column
    'AMPS_HALL
    Set Header = .Find("AMPS_HALL", LookIn:=xlValues)
    If Header Is Nothing Then AmpsHallCol = 0 Else AmsHallCol = Header.Column
    'OIL_PSI
    Set Header = .Find("OIL_PSI", LookIn:=xlValues)
    If Header Is Nothing Then OilPSICol = 0 Else OilPSICol = Header.Column
    'OIL_TEMP
    Set Header = .Find("OIL_TEMP", LookIn:=xlValues)
    If Header Is Nothing Then OiLTempCol = 0 Else OiLTempCol = Header.Column
    'CARB_TEMP
    Set Header = .Find("CARB_TEMP", LookIn:=xlValues)
    If Header Is Nothing Then CarbTempCol = 0 Else CarbTempCol = Header.Column
    'TANK_1
    Set Header = .Find("TANK_1", LookIn:=xlValues)
    If Header Is Nothing Then Tank1Col = 0 Else Tank1Col = Header.Column
    'TANK_2
    Set Header = .Find("TANK_2", LookIn:=xlValues)
    If Header Is Nothing Then Tank2Col = 0 Else Tank2Col = Header.Column
    'EGT_1
    Set Header = .Find("EGT_1", LookIn:=xlValues)
    If Header Is Nothing Then EGT1Col = 0 Else EGT1Col = Header.Column
    'EGT_2
    Set Header = .Find("EGT_2", LookIn:=xlValues)
    If Header Is Nothing Then EGT2Col = 0 Else EGT2Col = Header.Column
    'EGT_3
    Set Header = .Find("EGT_3", LookIn:=xlValues)
    If Header Is Nothing Then EGT3Col = 0 Else EGT3Col = Header.Column
    'EGT_4
    Set Header = .Find("EGT_4", LookIn:=xlValues)
    If Header Is Nothing Then EGT4Col = 0 Else EGT4Col = Header.Column
    'CHT_1
    Set Header = .Find("CHT_1", LookIn:=xlValues)
    If Header Is Nothing Then CHT1Col = 0 Else CHT1Col = Header.Column
    'CHT_2
    Set Header = .Find("CHT_2", LookIn:=xlValues)
    If Header Is Nothing Then CHT2Col = 0 Else CHT2Col = Header.Column
    'CHT_3
    Set Header = .Find("CHT_3", LookIn:=xlValues)
    If Header Is Nothing Then CHT3Col = 0 Else CHT3Col = Header.Column
    'CHT_4
    Set Header = .Find("CHT_4", LookIn:=xlValues)
    If Header Is Nothing Then CHT4Col = 0 Else CHT4Col = Header.Column
    'HP
    Set Header = .Find("HP", LookIn:=xlValues)
    If Header Is Nothing Then HPCol = 0 Else HPCol = Header.Column
    'ELEV_TRIM
    Set Header = .Find("ELEV_TRIM", LookIn:=xlValues)
    If Header Is Nothing Then ElevTrimCol = 0 Else ElevTrimCol = Header.Column
    'AIL_TRIM
    Set Header = .Find("AIL_TRIM", LookIn:=xlValues)
    If Header Is Nothing Then AilTrimCol = 0 Else AilTrimCol = Header.Column
    'FLAP_ANG
    Set Header = .Find("FLAP_ANG", LookIn:=xlValues)
    If Header Is Nothing Then FlapAngCol = 0 Else FlapAngCol = Header.Column
    'FLIGHT_TIME
    Set Header = .Find("FLIGHT_TIME", LookIn:=xlValues)
    If Header Is Nothing Then FlightTimeCol = 0 Else FlightTimeCol = Header.Column
    'TACH_TIME
    Set Header = .Find("TACH_TIME", LookIn:=xlValues)
    If Header Is Nothing Then TachTimeCol = 0 Else TachTimeCol = Header.Column
    'HOBBS_TIME
    Set Header = .Find("HOBBS_TIME", LookIn:=xlValues)
    If Header Is Nothing Then HobbsTimeCol = 0 Else HobbsTimeCol = Header.Column
    'TODAY_TIME
    Set Header = .Find("TODAY_TIME", LookIn:=xlValues)
    If Header Is Nothing Then TodayTimeCol = 0 Else TodayTimeCol = Header.Column
    'INPUTS_123
    Set Header = .Find("INPUTS_123", LookIn:=xlValues)
    If Header Is Nothing Then Inputs123Col = 0 Else Inputs123Col = Header.Column
    'PITCH
    Set Header = .Find("PITCH", LookIn:=xlValues)
    If Header Is Nothing Then PitchCol = 0 Else PitchCol = Header.Column
    'ROLL
    Set Header = .Find("ROLL", LookIn:=xlValues)
    If Header Is Nothing Then RollCol = 0 Else RollCol = Header.Column
    'HEADING
    Set Header = .Find("HEADING", LookIn:=xlValues)
    If Header Is Nothing Then HeadingCol = 0 Else HeadingCol = Header.Column
    'SLIP
    Set Header = .Find("SLIP", LookIn:=xlValues)
    If Header Is Nothing Then SlipCol = 0 Else SlipCol = Header.Column
    'G_METER
    Set Header = .Find("G_METER", LookIn:=xlValues)
    If Header Is Nothing Then GMeterCol = 0 Else GMeterCol = Header.Column
    'AHRS_STATUS
    Set Header = .Find("AHRS_STATUS", LookIn:=xlValues)
    If Header Is Nothing Then AHRSStatusCol = 0 Else AHRSStatusCol = Header.Column
    'LATITUDE
    Set Header = .Find("LATITUDE", LookIn:=xlValues)
    If Header Is Nothing Then LatitudeCol = 0 Else LatitudeCol = Header.Column
    'LONGITUDE
    Set Header = .Find("LONGITUDE", LookIn:=xlValues)
    If Header Is Nothing Then LongitudeCol = 0 Else LongitudeCol = Header.Column
    'GROUND_SPD
    Set Header = .Find("GROUND_SPD", LookIn:=xlValues)
    If Header Is Nothing Then GroundSpeedCol = 0 Else GroundSpeedCol = Header.Column
    'TRACK_DTRUE
    Set Header = .Find("TRACK_DTRUE", LookIn:=xlValues)
    If Header Is Nothing Then TrackDTureCol = 0 Else TrackDTrueCol = Header.Column
    'TRACK_DMAG
    Set Header = .Find("TRACK_DMAG", LookIn:=xlValues)
    If Header Is Nothing Then TrackDMagCol = 0 Else TrackDMagCol = Header.Column
End With

' These are the start and end rows for creating charts
StartRow = 2
EndRow = Cells.SpecialCells(xlCellTypeLastCell).Row - 1
Set TimeRange = Range(Cells(StartRow, TimeCol), Cells(EndRow, TimeCol))

' Add CHT and EGT Differential Columns
EndCol = ActiveSheet.UsedRange.Columns.Count
CHT1DiffCol = EndCol + 1
CHT2DiffCol = EndCol + 2
CHT3DiffCol = EndCol + 3
CHT4DiffCol = EndCol + 4
EGT1DiffCol = EndCol + 5
EGT2DiffCol = EndCol + 6
EGT3DiffCol = EndCol + 7
EGT4DiffCol = EndCol + 8
Cells(1, CHT1DiffCol).Value = "CHT1_Diff"
Cells(1, CHT2DiffCol).Value = "CHT2_Diff"
Cells(1, CHT3DiffCol).Value = "CHT3_Diff"
Cells(1, CHT4DiffCol).Value = "CHT4_Diff"
Cells(1, EGT1DiffCol).Value = "EGT1_Diff"
Cells(1, EGT2DiffCol).Value = "EGT2_Diff"
Cells(1, EGT3DiffCol).Value = "EGT3_Diff"
Cells(1, EGT4DiffCol).Value = "EGT4_Diff"
For AddRows = 2 To EndRow
    AverageCHT = WorksheetFunction.Average(Range(Cells(AddRows, CHT1Col), Cells(AddRows, CHT4Col)))
    Cells(AddRows, CHT1DiffCol).Value = Cells(AddRows, CHT1Col).Value - AverageCHT
    Cells(AddRows, CHT2DiffCol).Value = Cells(AddRows, CHT2Col).Value - AverageCHT
    Cells(AddRows, CHT3DiffCol).Value = Cells(AddRows, CHT3Col).Value - AverageCHT
    Cells(AddRows, CHT4DiffCol).Value = Cells(AddRows, CHT4Col).Value - AverageCHT
    AverageEGT = WorksheetFunction.Average(Range(Cells(AddRows, EGT1Col), Cells(AddRows, EGT4Col)))
    Cells(AddRows, EGT1DiffCol).Value = Cells(AddRows, EGT1Col).Value - AverageEGT
    Cells(AddRows, EGT2DiffCol).Value = Cells(AddRows, EGT2Col).Value - AverageEGT
    Cells(AddRows, EGT3DiffCol).Value = Cells(AddRows, EGT3Col).Value - AverageEGT
    Cells(AddRows, EGT4DiffCol).Value = Cells(AddRows, EGT4Col).Value - AverageEGT
Next AddRows


'Create CHT, OAT and Oil Temp Chart
Call CHT_OAT_OilTemp_Chart(DataSheetName, StartRow, EndRow, TimeCol, CHT1Col, CHT2Col, CHT3Col, CHT4Col, OATCol, OiLTempCol)

'Create EGT and RPM Chart
Call EGT_RPM_Chart(DataSheetName, StartRow, EndRow, TimeCol, EGT1Col, EGT2Col, EGT3Col, EGT4Col, RPMCol)

'Create CHT Differentials Chart
Call CHT_Diffs_Chart(DataSheetName, StartRow, EndRow, TimeCol, CHT1DiffCol, CHT2DiffCol, CHT3DiffCol, CHT4DiffCol)

'Create EGT Differentials Chart
Call EGT_Diffs_Chart(DataSheetName, StartRow, EndRow, TimeCol, EGT1DiffCol, EGT2DiffCol, EGT3DiffCol, EGT4DiffCol)

End Sub




Sub EGT_RPM_Chart(DataSheetName, StartRow, EndRow, TimeCol, EGT1Col, EGT2Col, EGT3Col, EGT4Col, RPMCol)

' EGT and RPM Chart Macro
' Graph of CHT OAT and OilTemps
'
'
Sheets(DataSheetName).Select
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
'Add EGT1
    .Chart.SetSourceData Source:=Range(Cells(StartRow, EGT1Col), Cells(EndRow, EGT1Col))
    .Chart.ChartType = xlLine
       
' Add timeline to X-Axis
    .Chart.SeriesCollection(1).XValues = Range(Cells(StartRow, TimeCol), Cells(EndRow, TimeCol))
' Add CHT2, CHT3, CHT4 and legend titles
    .Chart.SeriesCollection(1).Name = "=""EGT 1"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(2).Values = Range(Cells(StartRow, EGT2Col), Cells(EndRow, EGT2Col))
    .Chart.SeriesCollection(2).Name = "=""EGT 2"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(3).Values = Range(Cells(StartRow, EGT3Col), Cells(EndRow, EGT3Col))
    .Chart.SeriesCollection(3).Name = "=""EGT 3"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(4).Values = Range(Cells(StartRow, EGT4Col), Cells(EndRow, EGT4Col))
    .Chart.SeriesCollection(4).Name = "=""EGT 4"""
' Add OAT to graph
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(5).Name = "=""RPM"""
    .Chart.SeriesCollection(5).Values = Range(Cells(StartRow, RPMCol), Cells(EndRow, RPMCol))
End With
' Move chart to new page
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="EGT RPM"

End Sub

Sub CHT_OAT_OilTemp_Chart(DataSheetName, StartRow, EndRow, TimeCol, CHT1Col, CHT2Col, CHT3Col, CHT4Col, OATCol, OiLTempCol)

' CHT_OAT_OilT Macro
' Graph of CHT OAT and OilTemps
'
'
Sheets(DataSheetName).Select
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
'Add CHT1
    .Chart.SetSourceData Source:=Range(Cells(StartRow, CHT1Col), Cells(EndRow, CHT1Col))
    .Chart.ChartType = xlLine
       
' Add timeline to X-Axis
    .Chart.SeriesCollection(1).XValues = Range(Cells(StartRow, TimeCol), Cells(EndRow, TimeCol))
' Add CHT2, CHT3, CHT4 and legend titles
    .Chart.SeriesCollection(1).Name = "=""CHT 1"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(2).Values = Range(Cells(StartRow, CHT2Col), Cells(EndRow, CHT2Col))
    .Chart.SeriesCollection(2).Name = "=""CHT 2"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(3).Values = Range(Cells(StartRow, CHT3Col), Cells(EndRow, CHT3Col))
    .Chart.SeriesCollection(3).Name = "=""CHT 3"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(4).Values = Range(Cells(StartRow, CHT4Col), Cells(EndRow, CHT4Col))
    .Chart.SeriesCollection(4).Name = "=""CHT 4"""
' Add OAT to graph
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(5).Name = "=""OAT"""
    .Chart.SeriesCollection(5).Values = Range(Cells(StartRow, OATCol), Cells(EndRow, OATCol))
' Add Oil Temp to graph
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(6).Name = "=""Oil Temp"""
    .Chart.SeriesCollection(6).Values = Range(Cells(StartRow, OiLTempCol), Cells(EndRow, OiLTempCol))
End With
' Move chart to new page
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="CHT OAT OilTemp"
'Activate Data Sheet
    Sheets.Select

End Sub


Sub CHT_Diffs_Chart(DataSheetName, StartRow, EndRow, TimeCol, CHT1DiffCol, CHT2DiffCol, CHT3DiffCol, CHT4DiffCol)

' CHT differentials Chart
'
'
'
Sheets(DataSheetName).Select
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
'Add CHT1Diff
    .Chart.SetSourceData Source:=Range(Cells(StartRow, CHT1DiffCol), Cells(EndRow, CHT1DiffCol))
    .Chart.ChartType = xlLine
       
' Add timeline to X-Axis
    .Chart.SeriesCollection(1).XValues = Range(Cells(StartRow, TimeCol), Cells(EndRow, TimeCol))
' Add CHT2, CHT3, CHT4 and legend titles
    .Chart.SeriesCollection(1).Name = "=""CHT1 Diff"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(2).Values = Range(Cells(StartRow, CHT2DiffCol), Cells(EndRow, CHT2DiffCol))
    .Chart.SeriesCollection(2).Name = "=""CHT2 Diff"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(3).Values = Range(Cells(StartRow, CHT3DiffCol), Cells(EndRow, CHT3DiffCol))
    .Chart.SeriesCollection(3).Name = "=""CHT3 Diff"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(4).Values = Range(Cells(StartRow, CHT4DiffCol), Cells(EndRow, CHT4DiffCol))
    .Chart.SeriesCollection(4).Name = "=""CHT4 Diff"""
End With
' Move chart to new page
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="CHT Diff"

End Sub

Sub EGT_Diffs_Chart(DataSheetName, StartRow, EndRow, TimeCol, EGT1DiffCol, EGT2DiffCol, EGT3DiffCol, EGT4DiffCol)

' EGT differentials Chart
'
'
'
Sheets(DataSheetName).Select
With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
'Add EGT1Diff
    .Chart.SetSourceData Source:=Range(Cells(StartRow, EGT1DiffCol), Cells(EndRow, EGT1DiffCol))
    .Chart.ChartType = xlLine
       
' Add timeline to X-Axis
    .Chart.SeriesCollection(1).XValues = Range(Cells(StartRow, TimeCol), Cells(EndRow, TimeCol))
' Add EGT2, EGT3, EGT4 and legend titles
    .Chart.SeriesCollection(1).Name = "=""EGT1 Diff"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(2).Values = Range(Cells(StartRow, EGT2DiffCol), Cells(EndRow, EGT2DiffCol))
    .Chart.SeriesCollection(2).Name = "=""EGT2 Diff"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(3).Values = Range(Cells(StartRow, EGT3DiffCol), Cells(EndRow, EGT3DiffCol))
    .Chart.SeriesCollection(3).Name = "=""EGT3 Diff"""
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(4).Values = Range(Cells(StartRow, EGT4DiffCol), Cells(EndRow, EGT4DiffCol))
    .Chart.SeriesCollection(4).Name = "=""EGT4 Diff"""
End With
' Move chart to new page
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="EGT Diff"

End Sub

Sub Import_AFS_Data()

FileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to import", _
FileFilter:="AFS Data (*.ald),")
''
If FileToOpen = False Then
MsgBox "No file specified", vbExclamation, "AFS Data Import"
Exit Sub
Else
Workbooks.OpenText Filename:=FileToOpen, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False
End If

End Sub