title: Loop through all worksheets in all Excel workbooks in a folder to change the font, font size, and alignment of text in all cells
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>As it is now it seems to loop through the workbooks but it is not formating the text in any of them.  </p>
  
  <pre><code>    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
  </code></pre>
  
  <p>What changes do I need to make to have the macro work as intended?</p>
  
  <p>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?    </p>
  
  <p>Thanks for your help.</p>