CODEWITHSUNDEEP
vbaexcel
wjang4
wjang4

Reputation: 127

Writing a macro to change font and size in 500+ workbooks in one folder

I have 500+ .xlsx files in one folder; they have an identical format (charts and tables). What I need to do is to change the font, size, and alignment of specific cells. I've gotten a few macros from the internet, but none of them seems to work. The closest I think I got is below: 

Sub Font_Style()
    Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String

    fPath = "C:\xxx\1234\"
    fName = Dir(fPath & "*.xlsx")

    Do
        Set wb = Workbooks.Open(fName)
        Set sh = wb.Sheets("Empty Form")

        With sh.Range("X17")
                    .Font.Size = 20
                    .Font.Name = "Arial"
                    .Font.Bold = True
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
        End With

        wb.Close True
        fName = Dir
    Loop While fName <> ""
End Sub

Upvotes: 1

Views: 838

Answers (1)

Volkan Yurtseven
Volkan Yurtseven

Reputation: 444

You could use this

Sub formatchange()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("your path")

    Application.ScreenUpdating=False 'for a fster code
    For Each objFile In objFolder.files
      Workbooks.Open (objFile)
      'put your formatting code here        
      ActiveWorkbook.Close savechanges:=True        
    Next

    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing

    Application.ScreenUpdating=True 'turn on updatin again
End Sub

Upvotes: 1

Related Questions