S.H.A.D.O.
Well-known Member
- Joined
- Sep 6, 2005
- Messages
- 1,915
Hi everyone,
I am using Excel2007.
I want to loop through ALL the WorkBooks in a specified folder and change the column and row labels from their default which is Tahoma to Verdana and then Save the WorkBook.
I have put this code together from snippets I have found searching Google and doing a Macro record, but unfortunately I just can't seem to get it to work.
Any help will be greatly appreciated.
Thanks in advance.
I am using Excel2007.
I want to loop through ALL the WorkBooks in a specified folder and change the column and row labels from their default which is Tahoma to Verdana and then Save the WorkBook.
I have put this code together from snippets I have found searching Google and doing a Macro record, but unfortunately I just can't seem to get it to work.
Code:
Option Explicit
Sub Change_ColumnAndRow_Labels()
Dim wbCount As Long
Dim wbToChange As Workbook
Dim wbToUpdate As Workbook
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual
.DisplayAlerts = False: .EnableEvents = False
End With
On Error Resume Next
Set wbToUpdate = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\Users\My Name\Documents\My Name\Test Folder" ' Path of my folder to change.
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then ' Loop through folder until all WorkBooks are done.
For wbCount = 1 To .FoundFiles.Count
Set wbToChange = Workbooks.Open(Filename:=.FoundFiles(wbCount), UpdateLinks:=0)
With ActiveWorkbook.Styles("Normal")
.IncludeNumber = True
.IncludeFont = True ' <<< Don't think I need this.
.IncludeAlignment = True ' <<< Don't think I need this.
.IncludeBorder = True ' <<< Don't think I need this.
.IncludePatterns = True ' <<< Don't think I need this.
.IncludeProtection = True ' <<< Don't think I need this.
End With
With ActiveWorkbook.Styles("Normal").Font
.Name = "Verdana"
.Size = 10 ' <<< Don't think I need this.
.Bold = False ' <<< Don't think I need this.
.Italic = False ' <<< Don't think I need this.
.Underline = xlUnderlineStyleNone ' <<< Don't think I need this.
.Strikethrough = False ' <<< Don't think I need this.
.ThemeColor = 2 ' <<< Don't think I need this.
.TintAndShade = 0 ' <<< Don't think I need this.
.ThemeFont = xlThemeFontNone ' <<< Don't think I need this.
End With
wbToChange.Close SaveChanges:=False ' <<< Is this CORRECT!
Next wbCount
End If
End With
On Error GoTo 0
With Application
.EnableEvents = True: .DisplayAlerts = True
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
Any help will be greatly appreciated.
Thanks in advance.
Last edited: