excel - 将边框应用于文件夹中所有 excel 文件的宏

Question

我对 VBA/MACRO 非常陌生，我正在尝试创建一个宏，将边框放置/应用到文件夹中存在的所有 excel 文件。

标签： excelvba

Answer 1

试试这个：

    Dim FolderPath As String
    Dim StrFile As String
    
    ' Put your folder path here
    FolderPath = "C:\Temp"
    
    Dim xlWB As Excel.Workbook, xlWS As Excel.Worksheet
    
    StrFile = Dir(FolderPath)
    
    Do While Len(StrFile) > 0
        ' Basic check that this is a spreadsheet
        If Right(StrFile, 5) = ".xlsx" Then
            Set xlWB = Workbooks.Open(FolderPath & "/" & StrFile)
            
            For Each xlWS In xlWB.Worksheets
                If xlWS.Name = "POL" Or xlWS.Name = "POD" Then
                    With xlWS.UsedRange
                        With .Borders(xlEdgeLeft)
                            .LineStyle = xlContinuous
                            .ColorIndex = xlAutomatic
                            .Weight = xlThick
                        End With
                    End With
                End If
            Next
            
            xlWB.Close SaveChanges:=True
        End If
        
        StrFile = Dir()
    
    Loop

您应该考虑更好地检查和验证您找到的文件实际上是 Excel 工作簿。