遍历指定文件夹中的Excel

Public Sub deleteRows()
    Dim strPath As String
    strPath = "C:\backup\PCW\20140603\both\result"
        
    Dim objFSO As Scripting.FileSystemObject
    Set objFSO = New Scripting.FileSystemObject
    
    Dim objFolder As Scripting.Folder
    Set objFolder = objFSO.GetFolder(strPath)
    
    Dim i As Integer
    
    i = 0
    
    Dim objFile As Scripting.File
    For Each objFile In objFolder.Files
        Dim strFileExtension As String
        strFileExtension = Right(objFile.Path, 5)
        strFileExtension = LCase(strFileExtension)
        
        If strFileExtension = ".xlsx" Then
            'Call UpdateExcel(objFile.Path)
             Call Step7(objFile.Path)
            'MsgBox objFile.Path
            i = i + 1
        End If
    Next
        
    MsgBox i
    
End Sub


Private Sub UpdateExcel(ByVal ExcelFile As String)




    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    Dim lastLine As Integer
    
    'MsgBox "11"
    'set titile
    objSheet.Cells.Range("A1") = "Breakdown of calculation for rebate rectification (" & objSheet.Cells.Range("D39").Value & ")"
    
    objSheet.Cells.Range("E24") = objSheet.Cells.Range("E39").Value
    objSheet.Cells.Range("E25") = objSheet.Cells.Range("E40").Value
    objSheet.Cells.Range("E26") = objSheet.Cells.Range("E41").Value
    objSheet.Cells.Range("E27") = objSheet.Cells.Range("E42").Value
    objSheet.Cells.Range("E28") = objSheet.Cells.Range("E43").Value
    objSheet.Cells.Range("E29") = objSheet.Cells.Range("E44").Value
    objSheet.Cells.Range("E30") = objSheet.Cells.Range("E45").Value
    objSheet.Cells.Range("E31") = objSheet.Cells.Range("E46").Value
    objSheet.Cells.Range("E32") = objSheet.Cells.Range("E47").Value
    
    
    objSheet.Cells.Range("E55") = objSheet.Cells.Range("E39").Value
    objSheet.Cells.Range("E56") = objSheet.Cells.Range("E40").Value
    objSheet.Cells.Range("E57") = objSheet.Cells.Range("E41").Value
    objSheet.Cells.Range("E58") = objSheet.Cells.Range("E42").Value
    objSheet.Cells.Range("E59") = objSheet.Cells.Range("E43").Value
    objSheet.Cells.Range("E60") = objSheet.Cells.Range("E44").Value
    objSheet.Cells.Range("E61") = objSheet.Cells.Range("E45").Value
    objSheet.Cells.Range("E62") = objSheet.Cells.Range("E46").Value
    objSheet.Cells.Range("E63") = objSheet.Cells.Range("E47").Value
    
    objSheet.Cells.Range("E70") = objSheet.Cells.Range("E39").Value
    objSheet.Cells.Range("E71") = objSheet.Cells.Range("E40").Value
    objSheet.Cells.Range("E72") = objSheet.Cells.Range("E41").Value
    objSheet.Cells.Range("E73") = objSheet.Cells.Range("E42").Value
    objSheet.Cells.Range("E74") = objSheet.Cells.Range("E43").Value
    objSheet.Cells.Range("E75") = objSheet.Cells.Range("E44").Value
    objSheet.Cells.Range("E76") = objSheet.Cells.Range("E45").Value
    objSheet.Cells.Range("E77") = objSheet.Cells.Range("E46").Value
    objSheet.Cells.Range("E78") = objSheet.Cells.Range("E47").Value
    
    objSheet.Cells.Range("E86") = objSheet.Cells.Range("E39").Value
    objSheet.Cells.Range("E87") = objSheet.Cells.Range("E40").Value
    objSheet.Cells.Range("E88") = objSheet.Cells.Range("E41").Value
    objSheet.Cells.Range("E89") = objSheet.Cells.Range("E42").Value
    objSheet.Cells.Range("E90") = objSheet.Cells.Range("E43").Value
    objSheet.Cells.Range("E91") = objSheet.Cells.Range("E44").Value
    objSheet.Cells.Range("E92") = objSheet.Cells.Range("E45").Value
    objSheet.Cells.Range("E93") = objSheet.Cells.Range("E46").Value
    objSheet.Cells.Range("E94") = objSheet.Cells.Range("E47").Value
    
    objSheet.Cells.Range("E101") = objSheet.Cells.Range("E39").Value
    objSheet.Cells.Range("E102") = objSheet.Cells.Range("E40").Value
    objSheet.Cells.Range("E103") = objSheet.Cells.Range("E41").Value
    objSheet.Cells.Range("E104") = objSheet.Cells.Range("E42").Value
    objSheet.Cells.Range("E105") = objSheet.Cells.Range("E43").Value
    objSheet.Cells.Range("E106") = objSheet.Cells.Range("E44").Value
    objSheet.Cells.Range("E107") = objSheet.Cells.Range("E45").Value
    objSheet.Cells.Range("E108") = objSheet.Cells.Range("E46").Value
    objSheet.Cells.Range("E109") = objSheet.Cells.Range("E47").Value
    
    objSheet.Cells.Range("E117") = objSheet.Cells.Range("E39").Value
    objSheet.Cells.Range("E118") = objSheet.Cells.Range("E40").Value
    objSheet.Cells.Range("E119") = objSheet.Cells.Range("E41").Value
    objSheet.Cells.Range("E120") = objSheet.Cells.Range("E42").Value
    objSheet.Cells.Range("E121") = objSheet.Cells.Range("E43").Value
    objSheet.Cells.Range("E122") = objSheet.Cells.Range("E44").Value
    objSheet.Cells.Range("E123") = objSheet.Cells.Range("E45").Value
    objSheet.Cells.Range("E124") = objSheet.Cells.Range("E46").Value
    objSheet.Cells.Range("E125") = objSheet.Cells.Range("E47").Value
    
    
    objSheet.Cells.Range("F86") = Evaluate(objSheet.Cells.Range("F86").Formula)
    objSheet.Cells.Range("F87") = Evaluate(objSheet.Cells.Range("F87").Formula)
    objSheet.Cells.Range("F88") = Evaluate(objSheet.Cells.Range("F88").Formula)
    objSheet.Cells.Range("F89") = Evaluate(objSheet.Cells.Range("F89").Formula)
    objSheet.Cells.Range("F90") = Evaluate(objSheet.Cells.Range("F90").Formula)
    objSheet.Cells.Range("F91") = Evaluate(objSheet.Cells.Range("F91").Formula)
    objSheet.Cells.Range("F92") = Evaluate(objSheet.Cells.Range("F92").Formula)
    objSheet.Cells.Range("F93") = Evaluate(objSheet.Cells.Range("F93").Formula)
    objSheet.Cells.Range("F94") = Evaluate(objSheet.Cells.Range("F94").Formula)
    
    objSheet.Cells.Range("I86") = Evaluate(objSheet.Cells.Range("I86").Formula)
    objSheet.Cells.Range("I87") = Evaluate(objSheet.Cells.Range("I87").Formula)
    objSheet.Cells.Range("I88") = Evaluate(objSheet.Cells.Range("I88").Formula)
    objSheet.Cells.Range("I89") = Evaluate(objSheet.Cells.Range("I89").Formula)
    objSheet.Cells.Range("I90") = Evaluate(objSheet.Cells.Range("I90").Formula)
    objSheet.Cells.Range("I91") = Evaluate(objSheet.Cells.Range("I91").Formula)
    objSheet.Cells.Range("I92") = Evaluate(objSheet.Cells.Range("I92").Formula)
    objSheet.Cells.Range("I93") = Evaluate(objSheet.Cells.Range("I93").Formula)
    objSheet.Cells.Range("I94") = Evaluate(objSheet.Cells.Range("I94").Formula)
    
    objSheet.Cells.Range("L86") = Evaluate(objSheet.Cells.Range("L86").Formula)
    objSheet.Cells.Range("L87") = Evaluate(objSheet.Cells.Range("L87").Formula)
    objSheet.Cells.Range("L88") = Evaluate(objSheet.Cells.Range("L88").Formula)
    objSheet.Cells.Range("L89") = Evaluate(objSheet.Cells.Range("L89").Formula)
    objSheet.Cells.Range("L90") = Evaluate(objSheet.Cells.Range("L90").Formula)
    objSheet.Cells.Range("L91") = Evaluate(objSheet.Cells.Range("L91").Formula)
    objSheet.Cells.Range("L92") = Evaluate(objSheet.Cells.Range("L92").Formula)
    objSheet.Cells.Range("L93") = Evaluate(objSheet.Cells.Range("L93").Formula)
    objSheet.Cells.Range("L94") = Evaluate(objSheet.Cells.Range("L94").Formula)
    
    objSheet.Cells.Range("O86") = Evaluate(objSheet.Cells.Range("O86").Formula)
    objSheet.Cells.Range("O87") = Evaluate(objSheet.Cells.Range("O87").Formula)
    objSheet.Cells.Range("O88") = Evaluate(objSheet.Cells.Range("O88").Formula)
    objSheet.Cells.Range("O89") = Evaluate(objSheet.Cells.Range("O89").Formula)
    objSheet.Cells.Range("O90") = Evaluate(objSheet.Cells.Range("O90").Formula)
    objSheet.Cells.Range("O91") = Evaluate(objSheet.Cells.Range("O91").Formula)
    objSheet.Cells.Range("O92") = Evaluate(objSheet.Cells.Range("O92").Formula)
    objSheet.Cells.Range("O93") = Evaluate(objSheet.Cells.Range("O93").Formula)
    objSheet.Cells.Range("O94") = Evaluate(objSheet.Cells.Range("O94").Formula)
    
    objSheet.Cells.Range("R86") = Evaluate(objSheet.Cells.Range("R86").Formula)
    objSheet.Cells.Range("R87") = Evaluate(objSheet.Cells.Range("R87").Formula)
    objSheet.Cells.Range("R88") = Evaluate(objSheet.Cells.Range("R88").Formula)
    objSheet.Cells.Range("R89") = Evaluate(objSheet.Cells.Range("R89").Formula)
    objSheet.Cells.Range("R90") = Evaluate(objSheet.Cells.Range("R90").Formula)
    objSheet.Cells.Range("R91") = Evaluate(objSheet.Cells.Range("R91").Formula)
    objSheet.Cells.Range("R92") = Evaluate(objSheet.Cells.Range("R92").Formula)
    objSheet.Cells.Range("R93") = Evaluate(objSheet.Cells.Range("R93").Formula)
    objSheet.Cells.Range("R94") = Evaluate(objSheet.Cells.Range("R94").Formula)
    


    
    
    
    objSheet.Cells.Range("G70").ClearContents
    objSheet.Cells.Range("G71").ClearContents
    objSheet.Cells.Range("G72").ClearContents
    objSheet.Cells.Range("G73").ClearContents
    objSheet.Cells.Range("G74").ClearContents
    objSheet.Cells.Range("G75").ClearContents
    objSheet.Cells.Range("G76").ClearContents
    objSheet.Cells.Range("G77").ClearContents
    objSheet.Cells.Range("G78").ClearContents
    objSheet.Cells.Range("G79").ClearContents
    
    objSheet.Cells.Range("J70").ClearContents
    objSheet.Cells.Range("J71").ClearContents
    objSheet.Cells.Range("J72").ClearContents
    objSheet.Cells.Range("J73").ClearContents
    objSheet.Cells.Range("J74").ClearContents
    objSheet.Cells.Range("J75").ClearContents
    objSheet.Cells.Range("J76").ClearContents
    objSheet.Cells.Range("J77").ClearContents
    objSheet.Cells.Range("J78").ClearContents
    objSheet.Cells.Range("J79").ClearContents
    
    objSheet.Cells.Range("M70").ClearContents
    objSheet.Cells.Range("M71").ClearContents
    objSheet.Cells.Range("M72").ClearContents
    objSheet.Cells.Range("M73").ClearContents
    objSheet.Cells.Range("M74").ClearContents
    objSheet.Cells.Range("M75").ClearContents
    objSheet.Cells.Range("M76").ClearContents
    objSheet.Cells.Range("M77").ClearContents
    objSheet.Cells.Range("M78").ClearContents
    objSheet.Cells.Range("M79").ClearContents
    
    objSheet.Cells.Range("P70").ClearContents
    objSheet.Cells.Range("P71").ClearContents
    objSheet.Cells.Range("P72").ClearContents
    objSheet.Cells.Range("P73").ClearContents
    objSheet.Cells.Range("P74").ClearContents
    objSheet.Cells.Range("P75").ClearContents
    objSheet.Cells.Range("P76").ClearContents
    objSheet.Cells.Range("P77").ClearContents
    objSheet.Cells.Range("P78").ClearContents
    objSheet.Cells.Range("P79").ClearContents
    
    objSheet.Cells.Range("S70").ClearContents
    objSheet.Cells.Range("S71").ClearContents
    objSheet.Cells.Range("S72").ClearContents
    objSheet.Cells.Range("S73").ClearContents
    objSheet.Cells.Range("S74").ClearContents
    objSheet.Cells.Range("S75").ClearContents
    objSheet.Cells.Range("S76").ClearContents
    objSheet.Cells.Range("S77").ClearContents
    objSheet.Cells.Range("S78").ClearContents
    objSheet.Cells.Range("S79").ClearContents
    
    
    objSheet.Columns("D:D").Delete
    
                                   
    objBook.Save
    objBook.Close






End Sub


Private Sub UpdateOverPay(ByVal ExcelFile As String)


    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    If objSheet.Cells.Range("E48").Value = 0 Then
        objSheet.Range("E53:F125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
                
    End If
    
    If objSheet.Cells.Range("H48").Value = 0 Then
        objSheet.Range("H53:I125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("K48").Value = 0 Then
        objSheet.Range("K53:L125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("N48").Value = 0 Then
        objSheet.Range("N53:O125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("Q48").Value = 0 Then
        objSheet.Range("Q53:R125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    objBook.Save
    objBook.Close
    
End Sub


Private Sub UpdateGT(ByVal ExcelFile As String)


    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    If objSheet.Cells.Range("E64").Value > 0 Then
        objSheet.Range("E84:F125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
                
    End If
    
    If objSheet.Cells.Range("H64").Value > 0 Then
        objSheet.Range("H84:I125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("K64").Value > 0 Then
        objSheet.Range("K84:L125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("N64").Value > 0 Then
        objSheet.Range("N84:O125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("Q64").Value > 0 Then
        objSheet.Range("Q84:R125").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    objBook.Save
    objBook.Close
    
End Sub






Private Sub UpdateLT(ByVal ExcelFile As String)


    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    If objSheet.Cells.Range("E64").Value < 0 Then
        objSheet.Range("E68:F79").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
                
    End If
    
    If objSheet.Cells.Range("H64").Value < 0 Then
        objSheet.Range("H68:I79").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("K64").Value < 0 Then
        objSheet.Range("K68:L79").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("N64").Value < 0 Then
        objSheet.Range("N68:O79").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    If objSheet.Cells.Range("Q64").Value < 0 Then
        objSheet.Range("Q68:R79").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        Selection.ClearContents
    End If
    
    objBook.Save
    objBook.Close
    
End Sub




Private Sub Step5(ByVal ExcelFile As String)


    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    Dim rng As Range
    Set rng = objSheet.Range("S68:S79")
           
    With rng.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlThin
    End With
    
    Set rng = objSheet.Range("S115:S125")
           
    With rng.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .Weight = xlThin
    End With




    objBook.Save
    objBook.Close
    
End Sub




Private Sub Step6(ByVal ExcelFile As String)


    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    Dim lastLine As Integer
    For i = 125 To 117 Step -1
        If (objSheet.Cells(i, "D").Value) = "Y" Then
            lastLine = i
            Exit For
        End If
    Next i
    
    Dim rng As Range
    
    
    If objSheet.Cells.Range("E64").Value < 0 Then
        Set rng = objSheet.Range(objSheet.Cells(lastLine, "E"), objSheet.Cells(lastLine, "F"))
            
        With rng.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .Weight = xlThin
        End With
                
    End If
    
    If objSheet.Cells.Range("H64").Value < 0 Then
        Set rng = objSheet.Range(objSheet.Cells(lastLine, "H"), objSheet.Cells(lastLine, "I"))
            
        With rng.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .Weight = xlThin
        End With
    End If
    
    If objSheet.Cells.Range("K64").Value < 0 Then
        Set rng = objSheet.Range(objSheet.Cells(lastLine, "K"), objSheet.Cells(lastLine, "L"))
            
        With rng.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .Weight = xlThin
        End With
    End If
    
    If objSheet.Cells.Range("N64").Value < 0 Then
        Set rng = objSheet.Range(objSheet.Cells(lastLine, "N"), objSheet.Cells(lastLine, "O"))
            
        With rng.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .Weight = xlThin
        End With
    End If
    
    If objSheet.Cells.Range("Q64").Value < 0 Then
        Set rng = objSheet.Range(objSheet.Cells(lastLine, "Q"), objSheet.Cells(lastLine, "S"))
            
        With rng.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .Weight = xlThin
        End With
    End If
               
    
    For i = 125 To 117 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    


    
    
    For i = 109 To 101 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
    
    For i = 94 To 86 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
    For i = 78 To 70 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
    For i = 63 To 55 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
    For i = 47 To 39 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
    For i = 32 To 24 Step -1
        If (objSheet.Cells(i, "D").Value) = "N" Then
            objSheet.Cells(i, "A").EntireRow.Delete
        End If
    Next i
    
    objSheet.Columns("D:D").Delete






    objBook.Save
    objBook.Close
    
End Sub


Private Sub Step7(ByVal ExcelFile As String)


    Dim objBook As Workbook
    Set objBook = Application.Workbooks.Open(ExcelFile)
    
    objBook.Activate
    
    Dim objSheet As Worksheet
    Set objSheet = objBook.Sheets("both")
    objSheet.Activate
    
    objSheet.Cells.Range("A1").Select


    objBook.Save
    objBook.Close
    
End Sub