Macro to run on all files in a folder but exclude sheets with PivotTable, Pivot Charts and Charts

[Nasir]

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
sheets.)

I have recorded the following macro for sheet

Sub Macro1()
'
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select

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
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

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
knowledge)

Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
.NewSearch
.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
Else
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

Nasir

 

[Donald Guillett]

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
sheets.)

I have recorded the following macro for sheet

Sub Macro1()
'
    Rows("1:1").Select
    Selection.Font.Bold = True
    Cells.Select

    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
Cells.Select
Cells.EntireColumn.AutoFit
    Range("A1").Select

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
knowledge)

Sub WorkWithFiles()
Dim as long
Dim wkbk as Workbook
With Application.FileSearch
  .NewSearch
  .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
  Else
        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

Nasir

As I recall, file seach does not work in xl2007 so I suggest looking
in the vba help index ( or Google excelIR )
http://www.google.com/#sclient=psy&...=f&oq=&pbx=1&bav=on.2,or.&fp=74b51816635ef892
and incorporate your sheet macro (modify to only change what is
needed), excluding sheet names desired.
"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Clif McIrvin]

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
sheets.)
[...]

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

I renamed your Macro1 as FormatWorksheet and modified it to run faster
by removing all the .Selects and .Activates except the last.

ScanWorkbooks and ProcessWorkbook are taken from existing routines that
I use on a regular basis ... I added code to use the status bar to
display the workbook and sheet names while the macro is running.

xOpenWorkbook is a function that returns false if it cannot open the
requested workbook; I leave that code for you as an exercise.

Enjoy!

Option Explicit

Private xSaveChanges As Boolean


Sub FormatWorksheet(sh As Worksheet)
'
sh.Rows("1:1").Font.Bold = True

With sh.Cells.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 sh.Cells.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

sh.Cells.EntireColumn.AutoFit
sh.Range("A1").Select

End Sub


Private Sub ScanWorkbooks()
Dim filename As String
Dim saveStatusBar As Boolean

saveStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True


' Get list of workbook files in "current" folder
filename = ActiveWorkbook.Path & "\*.xl??"
filename = Dir(filename)

Do
'Debug.Print filename
ProcessWorkbook ActiveWorkbook.Path & "\" & filename
filename = Dir()
Loop Until filename = ""

Application.DisplayStatusBar = saveStatusBar

End Sub

Sub ProcessWorkbook(LongName As String)

Dim s As Worksheet

If xOpenWorkbook(LongName:=LongName) Then 'do nothing if not open
'ActiveWorkbook is now LongName
xSaveChanges = True ' default is to save changes to workbook

With ActiveWorkbook
' Skip chart sheets
For Each s In .Worksheets
If s.ChartObjects.Count > 0 Then
Exit For ' bypass sheets that contain embedded charts
End If
If s.PivotTables.Count > 0 Then
Exit For ' bypass sheets that contain pivot tables
End If

Application.StatusBar = .Name & "!" & s.Name
FormatWorksheet s

Next s 'In ActiveWorkbook.Sheets

Application.StatusBar = False
.Close SaveChanges:=xSaveChanges
End With

End If 'Open LongName

End Sub

 

[Clif McIrvin]

I just took another look at your recorded macro and I'm not certain it
was doing what you want as you posted it. Replace what I posted with
this:

Option Explicit

Sub FormatWorksheet(sh As Worksheet)
'
With sh.Cells.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 sh.Rows("1:1")
.Font.Bold = True
With .Cells.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With

sh.Cells.EntireColumn.AutoFit
sh.Range("A1").Select

End Sub

 

[Nasir]

I just took another look at your recorded macro and I'm not certain it
was doing what you want as you posted it. Replace what I posted with
this:

Option Explicit

Sub FormatWorksheet(sh As Worksheet)
'
    With sh.Cells.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 sh.Rows("1:1")
        .Font.Bold = True
        With .Cells.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .Color = 65535
          .TintAndShade = 0
          .PatternTintAndShade = 0
        End With
    End With

    sh.Cells.EntireColumn.AutoFit
    sh.Range("A1").Select

End Sub

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir

 

[Nasir]

I just took another look at your recorded macro and I'm not certain it
was doing what you want as you posted it. Replace what I posted with
this:

Option Explicit

Sub FormatWorksheet(sh As Worksheet)
'
    With sh.Cells.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 sh.Rows("1:1")
        .Font.Bold = True
        With .Cells.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .Color = 65535
          .TintAndShade = 0
          .PatternTintAndShade = 0
        End With
    End With

    sh.Cells.EntireColumn.AutoFit
    sh.Range("A1").Select

End Sub

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir

 

[AB]

You need all the code (macros - as they're interdependant) as
elegantly written by Clif. The one that you need to launch it this:

ScanWorkbooks

It'll loop through the files and do what you need with the files.

 

[AB]

You need all the code (macros - as they're interdependant) as
elegantly written by Clif. The one that you need to launch it this:

ScanWorkbooks

It'll loop through the files and do what you need with the files.

 

[Clif McIrvin]

[...]

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir


Hi Nasir .... I won't attempt a detailed tutorial of what and why behind
the code I offered; but I will try to answer any specific questions you
have.

Start with AB's (Thank you AB!) reply to your post - launch
ScanWorkbooks which will call the other procedures as needed.

These newsgroups are a good place to ask questions --- there are many
here who are happy to help others learn. All we ask in return is for you
to try and understand our suggestions; and to ask specific questions
when we leave you confused.

 

[Clif McIrvin]

[...]

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir


Hi Nasir .... I won't attempt a detailed tutorial of what and why behind
the code I offered; but I will try to answer any specific questions you
have.

Start with AB's (Thank you AB!) reply to your post - launch
ScanWorkbooks which will call the other procedures as needed.

These newsgroups are a good place to ask questions --- there are many
here who are happy to help others learn. All we ask in return is for you
to try and understand our suggestions; and to ask specific questions
when we leave you confused.

 

[Nasir]

news:[email protected]...

[...]

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir

Hi Nasir .... I won't attempt a detailed tutorial of what and why behind
the code I offered; but I will try to answer any specific questions you
have.

Start with AB's (Thank you AB!) reply to your post - launch
ScanWorkbooks which will call the other procedures as needed.

These newsgroups are a good place to ask questions --- there are many
here who are happy to help others learn. All we ask in return is for you
to try and understand our suggestions; and to ask specific questions
when we leave you confused.

Hello Clif and AB
Thanks for the reply
I posted the 3 macros in a module
1) ScanWorkbooks
2) ProcessWorkbooks
3) FormatWorksheet

When I run ScanWorkbooks it stops at this line giving error....Sub or
Function not defined

If xOpenWorkbook(LongName:=LongName)

I need your help please

Thanks
Nasir

 

[Clif McIrvin]

news:[email protected]...

[...]

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir

Hi Nasir .... I won't attempt a detailed tutorial of what and why
behind
the code I offered; but I will try to answer any specific questions
you
have.

Start with AB's (Thank you AB!) reply to your post - launch
ScanWorkbooks which will call the other procedures as needed.

These newsgroups are a good place to ask questions --- there are many
here who are happy to help others learn. All we ask in return is for
you
to try and understand our suggestions; and to ask specific questions
when we leave you confused.

Hello Clif and AB
Thanks for the reply
I posted the 3 macros in a module
1) ScanWorkbooks
2) ProcessWorkbooks
3) FormatWorksheet

When I run ScanWorkbooks it stops at this line giving error....Sub or
Function not defined

If xOpenWorkbook(LongName:=LongName)

I need your help please
------------


Ah. Yes, I did leave that one out, and by the time I read your post I'd
forgotten that little detail.

From my original reply:
"xOpenWorkbook is a function that returns false if it cannot open the
requested workbook; I leave that code for you as an exercise."

My function first checks to see if the requested workbook is already
open. If it is not open, it attempts to open it. If the workbook was
already open, or it is able to open it successfully it returns True; if
it cannot open the workbook it returns False.

You can try replacing

If xOpenWorkbook(LongName:=LongName) Then 'do nothing if not open

with

Workbooks.Open FileName:=LongName

which will open the workbook; but has no error handling. Also, you will
need to delete the line:

End If 'Open LongName

If you wish to try and write the xOpenWorkbook function yourself, I'd
suggest that you start by using the macro recorder to record opening a
workbook and modify the generated code, and read the help on the
Workbooks.Open method. If you have never written a function before, read
the help on the function statement.

Post back if you need more assistance.

 

[Nasir]

"Nasir" <[email protected]> wrote in message

[...]

I cannot make it work
There are three differenct macros now..
I wanted to have one macro for my purpose..pardon my ignorance please

Nasir

Hi Nasir .... I won't attempt a detailed tutorial of what and why
behind
the code I offered; but I will try to answer any specific questions
you
have.

Start with AB's (Thank you AB!) reply to your post - launch
ScanWorkbooks which will call the other procedures as needed.

These newsgroups are a good place to ask questions --- there are many
here who are happy to help others learn. All we ask in return is for
you
to try and understand our suggestions; and to ask specific questions
when we leave you confused.

(clare reads his mail with moe, nomail feeds the bit bucket

Hello Clif and AB
Thanks for the reply
I posted the 3 macros in a module
1) ScanWorkbooks
2) ProcessWorkbooks
3) FormatWorksheet

When I run ScanWorkbooks it stops at this line giving error....Sub or
Function not defined

If xOpenWorkbook(LongName:=LongName)

I need your help please
------------

Ah. Yes, I did leave that one out, and by the time I read your post I'd
forgotten that little detail.

From my original reply:
"xOpenWorkbook is a function that returns false if it cannot open the
requested workbook; I leave that code for you as an exercise."

My function first checks to see if the requested workbook is already
open. If it is not open, it attempts to open it. If the workbook was
already open, or it is able to open it successfully it returns True; if
it cannot open the workbook it returns False.

You can try replacing

If xOpenWorkbook(LongName:=LongName) Then 'do nothing if not open

with

Workbooks.Open FileName:=LongName

which will open the workbook; but has no error handling. Also, you will
need to delete the line:

End If 'Open LongName

If you wish to try and write the xOpenWorkbook function yourself, I'd
suggest that you start by using the macro recorder to record opening a
workbook and modify the generated code, and read the help on the
Workbooks.Open method. If you have never written a function before, read
the help on the function statement.

Post back if you need more assistance.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket - Hide quoted text -

- Show quoted text -

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
“Select method of Range class failed”
sh.Range("A2").Select <<< this line is highlighted>>>

Can you guide me please?

Nasir

 

[Clif McIrvin]

"Nasir" <[email protected]> wrote in message

[...]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
“Select method of Range class failed”
sh.Range("A2").Select <<< this line is highlighted>>>

Can you guide me please?


-----------

That's the last line of FormatWorksheet, isn't it? So it appears that
everything else is doing what you need.

Try changing that line to

sh.Range("A2").Activate

and see if that works. If that doesn't fix it post back.

 

[Clif McIrvin]

"Nasir" <[email protected]> wrote in message

[ ]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
“Select method of Range class failed”
sh.Range("A2").Select <<< this line is highlighted>>>

Can you guide me please?

Hi Nasir

You're welcome.

I did some testing .... Range.Select and Range.Activate do not work
unless the sheet is the active sheet .... so change that one line to
these two lines:

sh.Activate
sh.Range("A2").Activate

 

[Nasir]

[ ]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
Select method of Range class failed
sh.Range("A2").Select <<< this line is highlighted>>>

Can you guide me please?

Hi Nasir

You're welcome.

I did some testing .... Range.Select and Range.Activate do not work
unless the sheet is the active sheet .... so change that one line to
these two lines:

sh.Activate
sh.Range("A2").Activate

Thank you sooooooooooooooooooo much
Worked like a Magician Wand

Appreciate the great help from you.
Nasir

 

[Clif McIrvin]

[ ]

Hi Clif
Thanks for your help
I changed the lines as per your instructions.

When I run the Private Sub ScanWorkbooks
I get Runtime error 1004 in Sub FormatWorksheet
Select method of Range class failed
sh.Range("A2").Select <<< this line is highlighted>>>

Can you guide me please?

Hi Nasir

You're welcome.

I did some testing .... Range.Select and Range.Activate do not work
unless the sheet is the active sheet .... so change that one line to
these two lines:

sh.Activate
sh.Range("A2").Activate

Thank you sooooooooooooooooooo much
Worked like a Magician Wand

Appreciate the great help from you.
Nasir


You're welcome. Thanks for feeding back.