View Single Post
  #1   Report Post  
Old March 5th 11, 09:31 PM posted to microsoft.public.excel.programming
Nasir[_2_] Nasir[_2_] is offline
external usenet poster
First recorded activity by ExcelBanter: Dec 2010
Posts: 9
Default Macro to run on all files in a folder but exclude sheets with PivotTable, Pivot Charts and Charts

Hello All,
I am using Excel 2007 and have the following problem.

I have many excel files in a folder C:\MyData.

I want to format the data in all sheets in all excel files in MyData
Folder as follows:
1) Whole sheet will be Font Size 11, Trebuchet MS.
2) First Row would be bold and with cell color in yellow
3) Columns to be Autofit for the whole sheet
4) Cell pointer to be in A1 before saving the file.

Each workbook has many sheets which includes pivot table, charts and
pivot charts.

I want to run the macro to open all Excel files in MyData folder and
run on all sheets (excluding the pivot table, chart and Pivot Chart

I have recorded the following macro for sheet

Sub Macro1()
Selection.Font.Bold = True

With Selection.Font
.Name = "Trebuchet MS"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub

I have seen the following macro which would open all files in a folder
- I am not sure whether it would work for all sheets (pardon my

Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
.LookIn = "C:\MyFolder"
.SearchSubFolders = False
.FileName = ".xls"
' .FileType = msoFileTypeAllFiles
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
set wkbk = Workbooks.Open(.Foundfiles(i))
' work with the wkbk reference
' macro1
wkbk.Close SaveChanges:=False
MsgBox "There were no files found."
End If
End With
End Sub

I wish the macro run on all sheets in the workbook but to exclude
Pivot Table, Pivot Charts and Chart Sheet.

Can anyone help me with this please?

Thanks in advance