循环遍历文件夹中所有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

问题描述

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

从我自己对VBA的知识有限，并且在这里阅读其他相关的问题，所以我已经把我以前存储在Personal.xls中的宏放在下面了。

它似乎循环通过工作簿，但它没有形成任何一个文本。

  Sub Format_Workbooks（）
 
'此宏要求对Microsoft脚本程序$ b $的引用b 
'在Tools\References下被选中，以使其工作。 
 
 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 
 
设置source = fso.GetFolder（C：\Documents and Settings\Thies\My Documents\Excel工作簿）
 
对于每个wbFile在source.Files 
 
如果fso.GetExtensionName（wbFile.Name）=xls然后
 
设置书= Workbooks.Open（wbFile.Path）
 
对于每个表单book.Sheets 
 
带表单
 
 .Cells.Font.Name =无论我要使用什么字体
 
 .Cells.Font.Size = 10 
 
 .Cells.Horizo​​ntalAlignment = xlLeft 
 
结束
 
下一张
 
 book.Close 
 
结束如果
 
下一个
 
 E nd Sub 
  

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

另外，由于我从来没有使用过微软脚本程序，而是在编写这个宏时所采用的方法是否正确，应该从头改写吗？

感谢您的帮助。

解决方案

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

根据Brett的建议编辑

  Sub FormatFiles（）
 Const fPath As String =D：\My Documents\
 Dim sh As Worksheet 
 Dim sName As String 
 
应用程序
 .Calculation = xlCalculationManual 
 .EnableEvents = False 
 .ScreenUpdating = False 
结束
 
 sName = Dir（fPath&* .xls *）
 
直到sName =
使用GetObject（fPath& sName）
对于每个sh in .Worksheets 
用sh 
 .Cells.Horizo​​ntalAlignment = xlLeft 
 .Cells.Font.Name =Tahoma
 .Cells.Font.Size = 10 
结束与
下一步$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 
 
 
 xlAutomatic 
 .EnableEvents = True 
 .ScreenUpdating = True 
 End with 
 End Sub 
  

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.

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.

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?

Thanks for your help.

解决方案

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:

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

这篇关于循环遍历文件夹中所有Excel工作簿中的所有工作表，以更改所有单元格中的字体，字体大小和文本对齐方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！