请问大神们用VBA怎么批量修改文件夹里面所有表格的页眉

忧陌啊

请问大神们用VBA怎么批量修改文件夹里面所有表格的页眉？
附件里面文件夹都是xlsx格式，4个表格的页眉都是2010年，我想通过vba实现将该文件夹的所有表格页眉改成2018年。
求大神指点！谢谢

hyqzzs

Sub 宏1()
    Dim MyPath As String, MyName As String
    MyPath = ThisWorkbook.Path
    MyName = Dir(MyPath & "\*.xlsx")
    Do While MyName <> ""
        Workbooks.Open MyPath & "\" & MyName
        With ActiveSheet.PageSetup
            .CenterHeader = "2018年我国省份"
        End With
        ActiveWorkbook.Close SaveChanges:=True
        MyName = Dir
    Loop
End Sub

试一试吧

忧陌啊

hyqzzs 发表于 2018-3-20 21:13
Sub 宏1()
    Dim MyPath As String, MyName As String
    MyPath = ThisWorkbook.Path

可以了，谢谢大神！

纳铭m

Sub demo()
    Dim File As String, WB As Workbook
    Dim DialogPath As String
    '核对
    Dim 所有文件 As String, File1 As String, 文件个数 As Long
   
    Const 文件格式 = "\*.xls*"
   
    Application.ScreenUpdating = False
    Application.DisplayScrollBars = True
   
    DialogPath = GetDirectory()
    If DialogPath = "" Then Exit Sub
   
    File = Dir(DialogPath & 文件格式)
    Do While File <> ""
    If File <> Workbooks(1).Windows(1).Caption Then
    Application.StatusBar = "正在处理..." & File
    Set WB = Workbooks.Open(DialogPath & "\" & File)
    If File1 <> File Then File1 = File: 所有文件 = 所有文件 & "  " & File1: 文件个数 = 文件个数 + 1
    WB.Sheets(1).PageSetup.CenterHeader = "2018年我国省份"
    WB.Close False
    End If
    File = Dir
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = ""
    MsgBox "文件个数:" & 文件个数 & "个" & vbNewLine & "所含文件:" & 所有文件
End Sub


Public Function GetDirectory() As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = -1 Then GetDirectory = .SelectedItems(1)
    End With
End Function


你看看有没用吧

忧陌啊

纳铭m 发表于 2018-3-20 22:02
Sub demo()
    Dim File As String, WB As Workbook
    Dim DialogPath As String

用了没改到额，二楼的可以用。