Open book, check for macros, close book

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

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
 
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
 
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 this3. 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
 
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
 
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
 
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
 
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
 
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
 
Back
Top