遍历指定文件夹中的Excel

最新推荐文章于 2022-10-14 23:21:28 发布
最新推荐文章于 2022-10-14 23:21:28 发布
阅读量514 收藏
点赞数
分类专栏： vba
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zxx2403/article/details/39431499
版权
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

zxx2403
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
导出Excel到指定文件夹
TuChuanLLuan的博客
07-29 1488
导出Excel到指定文件夹 package com.tool.tcl; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; impor...
遍历目录的三种方法
沉觞的博客
03-26 2244
1 #使用栈遍历目录 #根节点 path = r"E:\python1901\chengxu" #根节点先入栈 #判断栈是否为空 #出栈处理 #列举节点下所有的文件 #判断文件是否为目录, #若为目录,入栈处理 #若不为目录,直接打印 import os path = r"E:\python1901\chengxu" def stackgetdir(path): ...
Excel如何遍历提取文件夹目录的操作？
ffcell2016的博客
10-14 1485
今天要和大家分享的是，Excel如何遍历提取文件夹目录的操作？注意这里提取了文件夹目录后，还会自动创建文件夹的超链接，直接点击就打开了指定目录。9.好了单击其中一个目录就可以打开指定位置了。8.确定后即可看到带有超链接的文件夹目录。4.然后选择工作表中的文件目录操作。如果经验帮到了你，请记得分享！3.然后选择方方格子按钮操作。7.然后只勾选列出文件夹。2.打开一个空白的表格。5.弹出界面后浏览文件。6.选择要遍历的文件夹。
Excel VBA - 遍历某个文件夹中文件、文件夹及批量建立txt
热门推荐
11-03 9万+
我们可能会经常要将一个文件夹中的所有文件都遍历一遍，然后进行修改，下面就介绍用Dir函数实现遍历*.xlsx文件的方法 Dir 函数 返回一个 String，用以表示一个文件名、目录名或文件夹名称，它必须与指定的模式或文件属性、或磁盘卷标相匹配。 语法 Dir[(pathname[, attributes])] 第一个参数即是文件的地址，第一次引用的时候要标注，第二次用的时候就不必指出了
Excel Vba遍历文件夹内所有文件并修改内容
weixin_45514748的博客
06-29 3755
例如：在同一文件夹内的各文件，修改了B6位置的内容
遍历所有子文件夹查找XLS文件
11-25
vba 电子表格 宏代码 编程 用VBA代码实现遍历某文件夹下所有*.xls*文件，有两种实现方法
python遍历文件夹下所有excel文件
09-20
以下是一个简单的示例，展示如何遍历指定文件夹下的所有Excel文件： ```python import os import xlrd def collect_excel_files(directory): for filename in os.listdir(directory): if filename.endswith("....
Python3遍历指定文件夹下的excel文件，并把数据导入mysql数据库源码
06-26
遍历指定文件夹下的excel文件，打开excel文件之后读取多个sheet中指定的sheet数据，并把读取的数据导入到mysql数据库中,是python写的源码，环境是python3。
遍历文件夹生成excel文件统计表
07-21
1. 可以遍历指定文件夹的所有文件，支持嵌套目录，生成excel统计表，并添加超链接，可以点击打开，相当于一个目录索引 2. 文件统计表包含【层级】【文件名称】【文件路径】【属性】【大小】【创建时间】【上次修改】...
VBA将某文件夹下子文件夹的名称导入到excel表中
04-29
将某文件夹下子文件夹的名称导入到excel表中，语言是VBA
遍历指定文件夹下的XML，读取每个XML数据，生成到EXCEL导出，美化Excel单元格格式，合并单元格
03-21
1.遍历指定文件夹下的XML（由选择文件框FolderBrowserDialog指定文件路径） 2.读取每个XML数据，取出对应的客户单号 订单号等信息 3.生成到EXCEL导出（由选择文件框SavaFileDialog指定保存路径） 4.美化Excel单元格...
分享一下python遍历文件夹,读写Excel,压缩文件,上传百度云等操作
02-22
遍历文件夹：Python可以使用os模块中的函数来遍历指定路径下的文件和文件夹，以实现对文件系统的操作。 读写Excel：Python可以使用pandas库来读取和写入Excel文件，pandas是一个强大的数据分析工具，支持多种数据...
excel VBA基本操作：遍历当前文件夹下的excel文件
yihuliunian的博客
05-12 1247
转载自品略图书馆http://www.pinlue.com/article/2020/05/1214/5110507453707.html 经常用到excel，以下是遍历当前文件夹下的excel表。 Sub bl() Dim myPath As String, myFile As String Dim i As Integer Dim Arr() As String i = 0 myPath = ThisWorkbook.Path & "\*.xls*" myFile = Dir(myP..
遍历指定文件夹，逐个打开每一个excel文件，进行操作
Harlin的专栏
12-08 8897
Sub openExcel() Dim myPath$, myFile$, WB As Workbook Dim icount% icount = 0 Application.ScreenUpdating = False '冻结屏幕，以防屏幕抖动 myPath = "E:\" '把文件路径定义给变量 myFi
kettle遍历抽取某目录下Excel文件数据
yanmingzi的专栏
01-07 3155
第一步：新建获取文件名转换 1、设置文件路径及匹配正则表达式 2、获取字段 3、拖入复制记录到结果 第二步：新建抽取数据转换 1、设置文件目录变量 2、设置工作表名称 3、获取设置字段 4、设置表输出对象及字段映射 第三步：创建执行作业 1、拖入start控件 2、拖入转换控件设置获取文件目录 3、使用 JavaScript 设置变量 //获取上一步的信息列表 var result_list=previous_result.getRow
Excel VBA - export to UTF-8
zxx2403的专栏
11-13 1628
Dim filePath As String     Dim fileName As String     Dim charToEncode As String     Dim encodingType As String     Dim success As Boolean     Dim rngArray() As Variant     filePath = "C:\"
vba遍历文件夹中的excel
最新发布
09-05
上述代码使用了FileSystemObject对象来获取指定文件夹下的所有文件，并逐个打开并进行操作。你可以根据需要在"在这里添加你需要执行的操作"的部分进行相应的操作，比如修改文件名、读取数据等。记得在操作完成后关闭...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值