vba 循环遍历文件夹中所有 Excel 工作簿中的所有工作表，以更改所有单元格中的字体、字号和文本对齐方式

Loop through all worksheets in all Excel workbooks in a folder to change the font, font size, and alignment of text in all cells

提问by The Thing

On my hard drive I have a folder containing a number of Excel workbooks. I want to loop though all the worksheets in each of the Excel workbooks in this folder to change the font, font size, and alignment of text in all the cells.

在我的硬盘驱动器上，我有一个包含许多 Excel 工作簿的文件夹。我想遍历此文件夹中每个 Excel 工作簿中的所有工作表，以更改所有单元格中的字体、字体大小和文本对齐方式。

From my own limited knowledge of VBA and from reading other related questions here on SO I have cobbled toghether the macro below which I have stored in Personal.xls.

根据我自己对 VBA 的有限知识以及在 SO 上阅读其他相关问题，我拼凑了下面存储在 Personal.xls 中的宏。

As it is now it seems to loop through the workbooks but it is not formating the text in any of them.

就像现在一样，它似乎在循环工作簿，但它没有在其中任何一个中格式化文本。

    Sub Format_Workbooks()

    'This macro requires that a reference to Microsoft Scripting Routine

    'be selected under Tools\References in order for it to work.

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Dim fso As New FileSystemObject

    Dim source As Scripting.Folder

    Dim wbFile As Scripting.File

    Dim book As Excel.Workbook

    Dim sheet As Excel.Worksheet

    Set source = fso.GetFolder("C:\Documents and Settings\The Thing\My Documents\Excel Workbooks")

    For Each wbFile In source.Files

    If fso.GetExtensionName(wbFile.Name) = "xls" Then

      Set book = Workbooks.Open(wbFile.Path)

      For Each sheet In book.Sheets

        With sheet       

        .Cells.Font.Name = "Whatever font I want to use"

        .Cells.Font.Size = 10

        .Cells.HorizontalAlignment = xlLeft

        End With

      Next

      book.Close

    End If

    Next

End Sub

What changes do I need to make to have the macro work as intended?

我需要进行哪些更改才能使宏按预期工作？

Also, as I've never made use of the 'Microsoft Scripting Routine' before I'm wondering if the approach I've taken in writing this macro is correct for my stated goals or should it be rewritten from scratch?

此外，由于我从未使用过“Microsoft Scripting Routine”，因此我想知道我在编写此宏时所采用的方法是否符合我的既定目标，还是应该从头开始重写？

Thanks for your help.

谢谢你的帮助。

回答by SWa

If the file types are mixed you may get an increase in performance with the Dir function as you can filter the file type, something like:

如果文件类型是混合的，您可以使用 Dir 函数提高性能，因为您可以过滤文件类型，例如：

Edited as per Brett's suggestions

根据布雷特的建议编辑

Sub FormatFiles()
    Const fPath As String = "D:\My Documents\"
    Dim sh As Worksheet
    Dim sName As String

    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    sName = Dir(fPath & "*.xls*")

    Do Until sName = ""
        With GetObject(fPath & sName)
            For Each sh In .Worksheets
                With sh
                    .Cells.HorizontalAlignment = xlLeft
                    .Cells.Font.Name = "Tahoma"
                    .Cells.Font.Size = 10
                End With
            Next sh
            .Close True
        End With
        sName = Dir
    Loop

    With Application
        .Calculation = xlAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

回答by Tony Dallimore

The following statement means you do not see any warnings:

以下语句意味着您没有看到任何警告：

Application.DisplayAlerts = False

The warning you are missing is from:

您缺少的警告来自：

book.Close

which asks if you would like to save the changes you have made. By ignoring this question, you are answering "No".

这会询问您是否要保存所做的更改。忽略这个问题，你就是在回答“不”。

Recommended actions:

建议措施：

1. Delete Application.DisplayAlerts = False
2. Add book.Savebefore the close unless you want to confirm each save.

1. 删除 Application.DisplayAlerts = False
2. book.Save除非您想确认每次保存，否则在关闭之前添加。