Hello all,

I'm not so familiar with Excel macro programming (as is perhaps evident
by my question).

Is there a way to determine which Excel files have macros?

The reason for this request is that our firewall/antivirus appears to
be rejecting emails that have Excel attachments - only certain ones,
though. I'm thinking that the reason some Excel files are being
rejected is because they have a macro (or macros).

Thanks for your input,
Emilio

Emilio,

I wrote this a while back as a catch all function for workbooks, sheets or
charts. I'd probably do it differently now, but it seems to work, so why fix
it.

To make it work, you need to have the Trust Access to VB Project option on
in your security settings.

Function fnContainsMacros(vSheetOrBook As Variant) As Variant
Dim W As Workbook
Dim S As Worksheet
Dim C As Chart
Dim T As Variant
Dim cmpComponent As VBComponent
On Error Resume Next
Set W = vSheetOrBook
Set S = vSheetOrBook
Set C = vSheetOrBook
On Error GoTo 0
fnContainsMacros = False
If W Is Nothing And S Is Nothing And C Is Nothing Then
fnContainsMacros = False
GoTo EndRoutine
End If
If W Is Nothing Then
If S Is Nothing Then
Set T = C
Else
Set T = S
End If
On Error GoTo VBAccessDisabled
With T.Parent.VBProject
If .VBComponents.Count 0 Then
On Error GoTo 0
For Each cmpComponent In .VBComponents
If cmpComponent.CodeModule = T.CodeName Then
If cmpComponent.CodeModule.COUNTOFLINES 0 Then
fnContainsMacros = True
GoTo EndRoutine
End If
End If
Next cmpComponent
End If
On Error GoTo 0
End With
Else
On Error GoTo VBAccessDisabled
If W.VBProject.VBComponents.Count 0 Then fnContainsMacros = True
On Error GoTo 0
End If
EndRoutine:
Set C = Nothing
Set W = Nothing
Set S = Nothing
Set T = Nothing
Exit Function
VBAccessDisabled:
Err.Clear
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

--
Robin Hammond
www.enhanceddatasystems.com


wrote in message
ps.com...
Hello all,

I'm not so familiar with Excel macro programming (as is perhaps evident
by my question).

Is there a way to determine which Excel files have macros?

The reason for this request is that our firewall/antivirus appears to
be rejecting emails that have Excel attachments - only certain ones,
though. I'm thinking that the reason some Excel files are being
rejected is because they have a macro (or macros).

Thanks for your input,
Emilio

Very easy.

First the Security Level to Medium:

Tools Macros Security

Then attempt to open any file and you will be warned if the file contains
macros.
--
Gary's Student


" wrote:

Hello all,

I'm not so familiar with Excel macro programming (as is perhaps evident
by my question).

Is there a way to determine which Excel files have macros?

The reason for this request is that our firewall/antivirus appears to
be rejecting emails that have Excel attachments - only certain ones,
though. I'm thinking that the reason some Excel files are being
rejected is because they have a macro (or macros).

Thanks for your input,
Emilio

Hi Emilio,

If your firewall/antivirus is rejecting *incoming* files with macros, then you're not going to be able to get around it with another
macro, since you can't test the file before your firewall/antivirus has had a go at it.

Cheers

--
macropod
[MVP - Microsoft Word]


wrote in message ps.com...
| Hello all,
|
| I'm not so familiar with Excel macro programming (as is perhaps evident
| by my question).
|
| Is there a way to determine which Excel files have macros?
|
| The reason for this request is that our firewall/antivirus appears to
| be rejecting emails that have Excel attachments - only certain ones,
| though. I'm thinking that the reason some Excel files are being
| rejected is because they have a macro (or macros).
|
| Thanks for your input,
| Emilio
|

Robin Hammond: That looks nicely thorough, but a bit more involved than
I had in mind.
Gary's student: I like your suggestion. Easy and straightforward.
macropod: You're right to a certain extent. If the file is zipped, it
is allowed in.

Thanks to all for your input.
Emilio



macropod wrote:
Hi Emilio,

If your firewall/antivirus is rejecting *incoming* files with macros, then you're not going to be able to get around it with another
macro, since you can't test the file before your firewall/antivirus has had a go at it.

Cheers

--
macropod
[MVP - Microsoft Word]


wrote in message ps.com...
| Hello all,
|
| I'm not so familiar with Excel macro programming (as is perhaps evident
| by my question).
|
| Is there a way to determine which Excel files have macros?
|
| The reason for this request is that our firewall/antivirus appears to
| be rejecting emails that have Excel attachments - only certain ones,
| though. I'm thinking that the reason some Excel files are being
| rejected is because they have a macro (or macros).
|
| Thanks for your input,
| Emilio
|