Open book, check for macros, close book
 

Tiz nearly bedtime here too, but...

1. in the Visual Basic Editor, goto Tools, References, and select Microsoft
Visual Basic For Applications Extensibility from the list.
2. You got caught out by some word wrapping.
delete the comment that looks like this
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
3. In Excel, goto Tools, Options, Security, Macro Security, Trusted Sources,
and select Trust Access to Visual Basic Project.
4. Try it again and step through it using the F8 key in the editor to see
what is happening if it still returns incorrect results.

G'night

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Thanks for the help Robin..........

That stuff is 'way over my head........I just took it all and copied it
into
a regular module and put the Function on top of the Sub and changed the
file
names to ones I had and ran it..........I got "Compile Error: sub of\r
Function not defined" on the word "Extensibility".........so I REMed it
out
and re-ran and it flies, but I get "TRUE" on every workbook, whether or
not
they have VBA inside..........I don't understand the comment 'requires a
reference to Microsoft Visual Basic For Applications
Extensibility...........obviously I'm doing something wrong, but have no
clue as to what.......'tiz bedtime now, I'll look more tomorrow..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
Chuck,

A simple demo:

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = "C:\Test\Book1.xls"
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count 0 Then fnContainsMacros = True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Hi All...........

Still looking for some help on this one, if someone please. Under
macro
control, I wish to open another workbook, check to see if that workbook
contains any macros, and then close that workbook and record the answer
in
the first workbook.

TIA for assistance.........
Vaya con Dios,
Chuck, CABGx3

Open book, check for macros, close book
 

Okey Robin............thanks for coming back............I did all your
instructions except #3, I don't have those options here on XL2k,.......mine
is set up to "Trust all installed add-ins and templates", that appears to be
the only option...... maybe I will at work tomorrow on XL97............at
any rate, no more error messages and it seems to run fine now here, except
that it returns TRUE in all cases, whether there is code in the workbook or
not.........it just takes longer to do it in workbooks with a lot of
code............

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Robin Hammond" wrote in message
...
Tiz nearly bedtime here too, but...

1. in the Visual Basic Editor, goto Tools, References, and select
Microsoft
Visual Basic For Applications Extensibility from the list.
2. You got caught out by some word wrapping.
delete the comment that looks like this
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
3. In Excel, goto Tools, Options, Security, Macro Security, Trusted
Sources,
and select Trust Access to Visual Basic Project.
4. Try it again and step through it using the F8 key in the editor to see
what is happening if it still returns incorrect results.

G'night

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Thanks for the help Robin..........

That stuff is 'way over my head........I just took it all and copied it
into
a regular module and put the Function on top of the Sub and changed the
file
names to ones I had and ran it..........I got "Compile Error: sub of\r
Function not defined" on the word "Extensibility".........so I REMed it
out
and re-ran and it flies, but I get "TRUE" on every workbook, whether or
not
they have VBA inside..........I don't understand the comment 'requires
a
reference to Microsoft Visual Basic For Applications
Extensibility...........obviously I'm doing something wrong, but have no
clue as to what.......'tiz bedtime now, I'll look more
tomorrow..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
Chuck,

A simple demo:

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = "C:\Test\Book1.xls"
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count 0 Then fnContainsMacros = True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Hi All...........

Still looking for some help on this one, if someone please. Under
macro
control, I wish to open another workbook, check to see if that
workbook
contains any macros, and then close that workbook and record the
answer
in
the first workbook.

TIA for assistance.........
Vaya con Dios,
Chuck, CABGx3

Open book, check for macros, close book
 

Chuck,

Sorry about that. Looks like I didn't check it through completely. I didn't
realise that normal worksheets would show up in the component count. I've
now tested this on a few files and it seems reliable.

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = Application.GetOpenFilename
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
fnContainsMacros = False
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count wTest.Sheets.Count + 1 Then
fnContainsMacros = True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Okey Robin............thanks for coming back............I did all your
instructions except #3, I don't have those options here on
XL2k,.......mine
is set up to "Trust all installed add-ins and templates", that appears to
be
the only option...... maybe I will at work tomorrow on XL97............at
any rate, no more error messages and it seems to run fine now here, except
that it returns TRUE in all cases, whether there is code in the workbook
or
not.........it just takes longer to do it in workbooks with a lot of
code............

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Robin Hammond" wrote in message
...
Tiz nearly bedtime here too, but...

1. in the Visual Basic Editor, goto Tools, References, and select
Microsoft
Visual Basic For Applications Extensibility from the list.
2. You got caught out by some word wrapping.
delete the comment that looks like this
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
3. In Excel, goto Tools, Options, Security, Macro Security, Trusted
Sources,
and select Trust Access to Visual Basic Project.
4. Try it again and step through it using the F8 key in the editor to see
what is happening if it still returns incorrect results.

G'night

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Thanks for the help Robin..........

That stuff is 'way over my head........I just took it all and copied it
into
a regular module and put the Function on top of the Sub and changed the
file
names to ones I had and ran it..........I got "Compile Error: sub of\r
Function not defined" on the word "Extensibility".........so I REMed
it
out
and re-ran and it flies, but I get "TRUE" on every workbook, whether or
not
they have VBA inside..........I don't understand the comment 'requires
a
reference to Microsoft Visual Basic For Applications
Extensibility...........obviously I'm doing something wrong, but have
no
clue as to what.......'tiz bedtime now, I'll look more
tomorrow..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
Chuck,

A simple demo:

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = "C:\Test\Book1.xls"
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count 0 Then fnContainsMacros = True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Hi All...........

Still looking for some help on this one, if someone please. Under
macro
control, I wish to open another workbook, check to see if that
workbook
contains any macros, and then close that workbook and record the
answer
in
the first workbook.

TIA for assistance.........
Vaya con Dios,
Chuck, CABGx3

Open book, check for macros, close book
 

And this is even better! i.e. it works when there is code in a worksheet or
thisworkbook or chart I think.

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
Dim cmpTest As VBComponent
fnContainsMacros = False
On Error GoTo VBAccessDisabled
For Each cmpTest In wTest.VBProject.VBComponents
If cmpTest.CodeModule.CountOfLines 0 Then
fnContainsMacros = True
Exit Function
End If
Next cmpTest
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

Robin Hammond
www.enhanceddatasystems.com


"CLR" wrote in message
...
Okey Robin............thanks for coming back............I did all your
instructions except #3, I don't have those options here on
XL2k,.......mine
is set up to "Trust all installed add-ins and templates", that appears to
be
the only option...... maybe I will at work tomorrow on XL97............at
any rate, no more error messages and it seems to run fine now here,
except
that it returns TRUE in all cases, whether there is code in the workbook
or
not.........it just takes longer to do it in workbooks with a lot of
code............

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Robin Hammond" wrote in message
...
Tiz nearly bedtime here too, but...

1. in the Visual Basic Editor, goto Tools, References, and select
Microsoft
Visual Basic For Applications Extensibility from the list.
2. You got caught out by some word wrapping.
delete the comment that looks like this
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
3. In Excel, goto Tools, Options, Security, Macro Security, Trusted
Sources,
and select Trust Access to Visual Basic Project.
4. Try it again and step through it using the F8 key in the editor to
see
what is happening if it still returns incorrect results.

G'night

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Thanks for the help Robin..........

That stuff is 'way over my head........I just took it all and copied
it
into
a regular module and put the Function on top of the Sub and changed
the
file
names to ones I had and ran it..........I got "Compile Error: sub of\r
Function not defined" on the word "Extensibility".........so I REMed
it
out
and re-ran and it flies, but I get "TRUE" on every workbook, whether
or
not
they have VBA inside..........I don't understand the comment
'requires
a
reference to Microsoft Visual Basic For Applications
Extensibility...........obviously I'm doing something wrong, but have
no
clue as to what.......'tiz bedtime now, I'll look more
tomorrow..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
Chuck,

A simple demo:

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = "C:\Test\Book1.xls"
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count 0 Then fnContainsMacros =
True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Hi All...........

Still looking for some help on this one, if someone please. Under
macro
control, I wish to open another workbook, check to see if that
workbook
contains any macros, and then close that workbook and record the
answer
in
the first workbook.

TIA for assistance.........
Vaya con Dios,
Chuck, CABGx3

Open book, check for macros, close book
 

Thanks again Robin.........

I just got up and am headed for work.........I'll check these out later
today.
I appreciate your time and patience

Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
And this is even better! i.e. it works when there is code in a worksheet
or
thisworkbook or chart I think.

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
Dim cmpTest As VBComponent
fnContainsMacros = False
On Error GoTo VBAccessDisabled
For Each cmpTest In wTest.VBProject.VBComponents
If cmpTest.CodeModule.CountOfLines 0 Then
fnContainsMacros = True
Exit Function
End If
Next cmpTest
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

Robin Hammond
www.enhanceddatasystems.com


"CLR" wrote in message
...
Okey Robin............thanks for coming back............I did all your
instructions except #3, I don't have those options here on
XL2k,.......mine
is set up to "Trust all installed add-ins and templates", that appears
to
be
the only option...... maybe I will at work tomorrow on
XL97............at
any rate, no more error messages and it seems to run fine now here,
except
that it returns TRUE in all cases, whether there is code in the
workbook
or
not.........it just takes longer to do it in workbooks with a lot of
code............

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Robin Hammond" wrote in message
...
Tiz nearly bedtime here too, but...

1. in the Visual Basic Editor, goto Tools, References, and select
Microsoft
Visual Basic For Applications Extensibility from the list.
2. You got caught out by some word wrapping.
delete the comment that looks like this
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
3. In Excel, goto Tools, Options, Security, Macro Security, Trusted
Sources,
and select Trust Access to Visual Basic Project.
4. Try it again and step through it using the F8 key in the editor to
see
what is happening if it still returns incorrect results.

G'night

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Thanks for the help Robin..........

That stuff is 'way over my head........I just took it all and copied
it
into
a regular module and put the Function on top of the Sub and changed
the
file
names to ones I had and ran it..........I got "Compile Error: sub
of\r
Function not defined" on the word "Extensibility".........so I
REMed
it
out
and re-ran and it flies, but I get "TRUE" on every workbook, whether
or
not
they have VBA inside..........I don't understand the comment
'requires
a
reference to Microsoft Visual Basic For Applications
Extensibility...........obviously I'm doing something wrong, but
have
no
clue as to what.......'tiz bedtime now, I'll look more
tomorrow..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
Chuck,

A simple demo:

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = "C:\Test\Book1.xls"
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count 0 Then fnContainsMacros =
True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Hi All...........

Still looking for some help on this one, if someone please.
Under
macro
control, I wish to open another workbook, check to see if that
workbook
contains any macros, and then close that workbook and record the
answer
in
the first workbook.

TIA for assistance.........
Vaya con Dios,
Chuck, CABGx3

Open book, check for macros, close book
 

Allllllrighty then.....this one seems to do the job just fine. Many thanks
for hanging in there with me. Of note, I never was able to follow that
instruction #3 about the Trusted setting, but no matter, both the function
and macro run fine without doing it

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Robin Hammond" wrote:

And this is even better! i.e. it works when there is code in a worksheet or
thisworkbook or chart I think.

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
Dim cmpTest As VBComponent
fnContainsMacros = False
On Error GoTo VBAccessDisabled
For Each cmpTest In wTest.VBProject.VBComponents
If cmpTest.CodeModule.CountOfLines 0 Then
fnContainsMacros = True
Exit Function
End If
Next cmpTest
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

Robin Hammond
www.enhanceddatasystems.com


"CLR" wrote in message
...
Okey Robin............thanks for coming back............I did all your
instructions except #3, I don't have those options here on
XL2k,.......mine
is set up to "Trust all installed add-ins and templates", that appears to
be
the only option...... maybe I will at work tomorrow on XL97............at
any rate, no more error messages and it seems to run fine now here,
except
that it returns TRUE in all cases, whether there is code in the workbook
or
not.........it just takes longer to do it in workbooks with a lot of
code............

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Robin Hammond" wrote in message
...
Tiz nearly bedtime here too, but...

1. in the Visual Basic Editor, goto Tools, References, and select
Microsoft
Visual Basic For Applications Extensibility from the list.
2. You got caught out by some word wrapping.
delete the comment that looks like this
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
3. In Excel, goto Tools, Options, Security, Macro Security, Trusted
Sources,
and select Trust Access to Visual Basic Project.
4. Try it again and step through it using the F8 key in the editor to
see
what is happening if it still returns incorrect results.

G'night

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Thanks for the help Robin..........

That stuff is 'way over my head........I just took it all and copied
it
into
a regular module and put the Function on top of the Sub and changed
the
file
names to ones I had and ran it..........I got "Compile Error: sub of\r
Function not defined" on the word "Extensibility".........so I REMed
it
out
and re-ran and it flies, but I get "TRUE" on every workbook, whether
or
not
they have VBA inside..........I don't understand the comment
'requires
a
reference to Microsoft Visual Basic For Applications
Extensibility...........obviously I'm doing something wrong, but have
no
clue as to what.......'tiz bedtime now, I'll look more
tomorrow..........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Robin Hammond" wrote in message
...
Chuck,

A simple demo:

Sub Main()
Dim strFile As String
Dim wTest As Workbook
strFile = "C:\Test\Book1.xls"
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = strFile
Set wTest = Workbooks.Open(strFile, False)
.Cells(1, 2).Value = fnContainsMacros(wTest)
wTest.Close False
End With
End Sub

Function fnContainsMacros(wTest As Workbook) As Variant
'requires a reference to Microsoft Visual Basic For Applications
Extensibility
On Error GoTo VBAccessDisabled
If wTest.VBProject.VBComponents.Count 0 Then fnContainsMacros =
True
On Error GoTo 0
EndRoutine:
Exit Function

VBAccessDisabled:
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function

HTH,

Robin Hammond
www.enhanceddatasystems.com

"CLR" wrote in message
...
Hi All...........

Still looking for some help on this one, if someone please. Under
macro
control, I wish to open another workbook, check to see if that
workbook
contains any macros, and then close that workbook and record the
answer
in
the first workbook.

TIA for assistance.........
Vaya con Dios,
Chuck, CABGx3