View Single Post
  #9   Report Post  
Old March 7th 11, 11:42 PM posted to microsoft.public.excel.programming
Clif McIrvin[_3_] Clif McIrvin[_3_] is offline
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


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

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


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