View Single Post
  #10   Report Post  
Old March 10th 11, 06:16 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 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