Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 5th 11, 09:31 PM posted to microsoft.public.excel.programming
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
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

  #2   Report Post  
Old March 5th 11, 11:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 36
Default Macro to run on all files in a folder but exclude sheets withPivot Table, Pivot Charts and Charts

On Mar 5, 2:31*pm, Nasir wrote:
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&h...b51816635ef892
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."
  #3   Report Post  
Old March 6th 11, 01:58 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Nasir" wrote in message
...
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

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


  #4   Report Post  
Old March 6th 11, 02:15 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Clif McIrvin" wrote in message
...

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




--
Clif McIrvin

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


  #5   Report Post  
Old March 7th 11, 12:58 PM posted to microsoft.public.excel.programming
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 withPivot Table, Pivot Charts and Charts

On Mar 6, 5:15*am, "Clif McIrvin" wrote:
"Clif McIrvin" wrote in message

...

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

--
Clif McIrvin

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


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


  #6   Report Post  
Old March 7th 11, 02:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 236
Default Macro to run on all files in a folder but exclude sheets withPivot Table, Pivot Charts and Charts

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.



On Mar 7, 11:58*am, Nasir wrote:
On Mar 6, 5:15*am, "Clif McIrvin" wrote:





"Clif McIrvin" wrote in message


...


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


--
Clif McIrvin


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


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- Hide quoted text -

- Show quoted text -


  #7   Report Post  
Old March 7th 11, 03:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Nasir" wrote in message
...
On Mar 6, 5:15 am, "Clif McIrvin" wrote:
"Clif McIrvin" 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.

--
Clif McIrvin

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


  #8   Report Post  
Old March 7th 11, 09:46 PM posted to microsoft.public.excel.programming
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 withPivot Table, Pivot Charts and Charts

On Mar 7, 6:34*pm, "Clif McIrvin" wrote:
"Nasir" wrote in message

...
On Mar 6, 5:15 am, "Clif McIrvin" wrote: "Clif McIrvin" 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.

--
Clif McIrvin

(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

Thanks
Nasir
  #9   Report Post  
Old March 8th 11, 12:42 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 203
Default Macro to run on all files in a folder but exclude sheets with Pivot Table, Pivot Charts and Charts

"Nasir" wrote in message
...
On Mar 7, 6:34 pm, "Clif McIrvin" wrote:
"Nasir" wrote in message

...
On Mar 6, 5:15 am, "Clif McIrvin" wrote:
"Clif McIrvin" 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.

--
Clif McIrvin

(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 :-)


  #10   Report Post  
Old March 10th 11, 06:16 PM posted to microsoft.public.excel.programming
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 withPivot Table, Pivot Charts and Charts

On Mar 8, 3:42*am, "Clif McIrvin" wrote:
"Nasir" wrote in message

...
On Mar 7, 6:34 pm, "Clif McIrvin" wrote:





"Nasir" wrote in message


....
On Mar 6, 5:15 am, "Clif McIrvin" wrote:
"Clif McIrvin" 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.


--
Clif McIrvin


(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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to group data in pivot charts MichaelR Excel Programming 0 June 16th 08 10:37 PM
pie charts automatically exclude zero values Cris[_2_] Charts and Charting in Excel 1 March 4th 08 03:43 AM
PivotTable Charts Joey Excel Discussion (Misc queries) 1 February 12th 08 12:30 PM
charts exclude cell with formula linda Charts and Charting in Excel 0 September 19th 07 05:12 AM
macro for printing pivot charts marina madeleine Excel Programming 0 October 15th 04 02:03 PM


All times are GMT +1. The time now is 04:03 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright ©2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017