Open book, check for macros, close book

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> >
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >> > 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
>>> >> >
>>> >> >
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

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" <[email protected]> wrote in message
news:unU#[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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" <[email protected]> wrote in message
> >>> >> news:[email protected]...
> >>> >> > 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
> >>> >> >
> >>> >> >
> >>> >> >
> >>> >> >
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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" <[email protected]> wrote in message
> >>> >> news:[email protected]...
> >>> >> > 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
> >>> >> >
> >>> >> >
> >>> >> >
> >>> >> >
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>