Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check to see if a Workbook has a Macro
I've got a list of approximately 12000 Workbooks. I want to be able to find
out which contain macros. Apart from opening each one manually is there a way i can open the Workbook and programatically check to see if a macro exists. I'm not concerned about number of procedures forms, Modules etc, just to see if it contains even a recorded macro. Thanks for any suggestions on this. Regards, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check to see if a Workbook has a Macro
I took one of Pearsons macro and made some minor changes. Make sure yoiu
follow the instructions in comments below. 'set reference in Tools - Reference 'Microsoft Visual Basic For Applications Extensibility 5.3 'Next, you need to enable programmatic access to the VBA Project. 'In Excel 2003 and earlier, 'go the Tools menu (in Excel, not in the VBA editor), ' 'choose Macros and then the Security item. 'In that dialog, click on the Trusted Publishers tab and 'check the Trust access to the Visual Basic Project setting. 'In Excel 2007, click the Developer item on the main Ribbon and 'then click the Macro Security item in the Code panel. 'In that dialog, choose Macro Settings and check the Trust access 'to the VBA project object model. Sub TestForMacros() Dim VBComp As VBIDE.VBComponent Set SumSht = ThisWorkbook.ActiveSheet Folder = "C:\Documents and Settings\All\" RowCount = 1 FName = Dir(Folder & "*.xls*") Do While FName < "" Set BK = Workbooks.Open(Filename:=Folder & FName) Set VBProj = BK.VBProject Found = False For Each VBComp In VBProj.VBComponents LineCount = TotalCodeLinesInVBComponent(VBComp) If LineCount 0 Then SumSht.Range("A" & RowCount) = FName RowCount = RowCount + 1 Exit For End If Next VBComp BK.Close savechanges:=False FName = Dir() Loop End Sub Public Function TotalCodeLinesInVBComponent(VBComp As VBIDE.VBComponent) As Long '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' ' This returns the total number of code lines (excluding blank lines and ' comment lines) in the VBComponent referenced by VBComp. Returns -1 ' if the VBProject is locked. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' Dim N As Long Dim S As String Dim LineCount As Long If VBComp.Collection.Parent.Protection = vbext_pp_locked Then TotalCodeLinesInVBComponent = -1 Exit Function End If With VBComp.CodeModule For N = 1 To .CountOfLines S = .Lines(N, 1) If Trim(S) = vbNullString Then ' blank line, skip it ElseIf Left(Trim(S), 1) = "'" Then ' comment line, skip it Else LineCount = LineCount + 1 End If Next N End With TotalCodeLinesInVBComponent = LineCount End Function "John" wrote: I've got a list of approximately 12000 Workbooks. I want to be able to find out which contain macros. Apart from opening each one manually is there a way i can open the Workbook and programatically check to see if a macro exists. I'm not concerned about number of procedures forms, Modules etc, just to see if it contains even a recorded macro. Thanks for any suggestions on this. Regards, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check to see if a Workbook has a Macro
I like to add this to this thread
In Excel 2007 you can use the new HasVBProject -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joel" wrote in message ... I took one of Pearsons macro and made some minor changes. Make sure yoiu follow the instructions in comments below. 'set reference in Tools - Reference 'Microsoft Visual Basic For Applications Extensibility 5.3 'Next, you need to enable programmatic access to the VBA Project. 'In Excel 2003 and earlier, 'go the Tools menu (in Excel, not in the VBA editor), ' 'choose Macros and then the Security item. 'In that dialog, click on the Trusted Publishers tab and 'check the Trust access to the Visual Basic Project setting. 'In Excel 2007, click the Developer item on the main Ribbon and 'then click the Macro Security item in the Code panel. 'In that dialog, choose Macro Settings and check the Trust access 'to the VBA project object model. Sub TestForMacros() Dim VBComp As VBIDE.VBComponent Set SumSht = ThisWorkbook.ActiveSheet Folder = "C:\Documents and Settings\All\" RowCount = 1 FName = Dir(Folder & "*.xls*") Do While FName < "" Set BK = Workbooks.Open(Filename:=Folder & FName) Set VBProj = BK.VBProject Found = False For Each VBComp In VBProj.VBComponents LineCount = TotalCodeLinesInVBComponent(VBComp) If LineCount 0 Then SumSht.Range("A" & RowCount) = FName RowCount = RowCount + 1 Exit For End If Next VBComp BK.Close savechanges:=False FName = Dir() Loop End Sub Public Function TotalCodeLinesInVBComponent(VBComp As VBIDE.VBComponent) As Long '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' ' This returns the total number of code lines (excluding blank lines and ' comment lines) in the VBComponent referenced by VBComp. Returns -1 ' if the VBProject is locked. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' Dim N As Long Dim S As String Dim LineCount As Long If VBComp.Collection.Parent.Protection = vbext_pp_locked Then TotalCodeLinesInVBComponent = -1 Exit Function End If With VBComp.CodeModule For N = 1 To .CountOfLines S = .Lines(N, 1) If Trim(S) = vbNullString Then ' blank line, skip it ElseIf Left(Trim(S), 1) = "'" Then ' comment line, skip it Else LineCount = LineCount + 1 End If Next N End With TotalCodeLinesInVBComponent = LineCount End Function "John" wrote: I've got a list of approximately 12000 Workbooks. I want to be able to find out which contain macros. Apart from opening each one manually is there a way i can open the Workbook and programatically check to see if a macro exists. I'm not concerned about number of procedures forms, Modules etc, just to see if it contains even a recorded macro. Thanks for any suggestions on this. Regards, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check to see if a Workbook has a Macro
Ron: I was wondering when I read this request did the person want to say
there was a macro in the workbook if there is a module with no code? Excel says there is a macro in the workbook as long as a module exists with no code. I haven't used HasVBProject . Does it respond with a project if there is no code? What happens if the workbook is excel 2003 with a xls extension? Excel 2007 only has a project if it is xlsx. "Ron de Bruin" wrote: I like to add this to this thread In Excel 2007 you can use the new HasVBProject -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joel" wrote in message ... I took one of Pearsons macro and made some minor changes. Make sure yoiu follow the instructions in comments below. 'set reference in Tools - Reference 'Microsoft Visual Basic For Applications Extensibility 5.3 'Next, you need to enable programmatic access to the VBA Project. 'In Excel 2003 and earlier, 'go the Tools menu (in Excel, not in the VBA editor), ' 'choose Macros and then the Security item. 'In that dialog, click on the Trusted Publishers tab and 'check the Trust access to the Visual Basic Project setting. 'In Excel 2007, click the Developer item on the main Ribbon and 'then click the Macro Security item in the Code panel. 'In that dialog, choose Macro Settings and check the Trust access 'to the VBA project object model. Sub TestForMacros() Dim VBComp As VBIDE.VBComponent Set SumSht = ThisWorkbook.ActiveSheet Folder = "C:\Documents and Settings\All\" RowCount = 1 FName = Dir(Folder & "*.xls*") Do While FName < "" Set BK = Workbooks.Open(Filename:=Folder & FName) Set VBProj = BK.VBProject Found = False For Each VBComp In VBProj.VBComponents LineCount = TotalCodeLinesInVBComponent(VBComp) If LineCount 0 Then SumSht.Range("A" & RowCount) = FName RowCount = RowCount + 1 Exit For End If Next VBComp BK.Close savechanges:=False FName = Dir() Loop End Sub Public Function TotalCodeLinesInVBComponent(VBComp As VBIDE.VBComponent) As Long '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' ' This returns the total number of code lines (excluding blank lines and ' comment lines) in the VBComponent referenced by VBComp. Returns -1 ' if the VBProject is locked. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' Dim N As Long Dim S As String Dim LineCount As Long If VBComp.Collection.Parent.Protection = vbext_pp_locked Then TotalCodeLinesInVBComponent = -1 Exit Function End If With VBComp.CodeModule For N = 1 To .CountOfLines S = .Lines(N, 1) If Trim(S) = vbNullString Then ' blank line, skip it ElseIf Left(Trim(S), 1) = "'" Then ' comment line, skip it Else LineCount = LineCount + 1 End If Next N End With TotalCodeLinesInVBComponent = LineCount End Function "John" wrote: I've got a list of approximately 12000 Workbooks. I want to be able to find out which contain macros. Apart from opening each one manually is there a way i can open the Workbook and programatically check to see if a macro exists. I'm not concerned about number of procedures forms, Modules etc, just to see if it contains even a recorded macro. Thanks for any suggestions on this. Regards, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check to see if a Workbook has a Macro
Hi Joel
If there is a empty module it say True It is also working for Excel 97-2003 workbooks that you open in Excel 2007 I use the code here http://www.rondebruin.nl/saveas.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joel" wrote in message ... Ron: I was wondering when I read this request did the person want to say there was a macro in the workbook if there is a module with no code? Excel says there is a macro in the workbook as long as a module exists with no code. I haven't used HasVBProject . Does it respond with a project if there is no code? What happens if the workbook is excel 2003 with a xls extension? Excel 2007 only has a project if it is xlsx. "Ron de Bruin" wrote: I like to add this to this thread In Excel 2007 you can use the new HasVBProject -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Joel" wrote in message ... I took one of Pearsons macro and made some minor changes. Make sure yoiu follow the instructions in comments below. 'set reference in Tools - Reference 'Microsoft Visual Basic For Applications Extensibility 5.3 'Next, you need to enable programmatic access to the VBA Project. 'In Excel 2003 and earlier, 'go the Tools menu (in Excel, not in the VBA editor), ' 'choose Macros and then the Security item. 'In that dialog, click on the Trusted Publishers tab and 'check the Trust access to the Visual Basic Project setting. 'In Excel 2007, click the Developer item on the main Ribbon and 'then click the Macro Security item in the Code panel. 'In that dialog, choose Macro Settings and check the Trust access 'to the VBA project object model. Sub TestForMacros() Dim VBComp As VBIDE.VBComponent Set SumSht = ThisWorkbook.ActiveSheet Folder = "C:\Documents and Settings\All\" RowCount = 1 FName = Dir(Folder & "*.xls*") Do While FName < "" Set BK = Workbooks.Open(Filename:=Folder & FName) Set VBProj = BK.VBProject Found = False For Each VBComp In VBProj.VBComponents LineCount = TotalCodeLinesInVBComponent(VBComp) If LineCount 0 Then SumSht.Range("A" & RowCount) = FName RowCount = RowCount + 1 Exit For End If Next VBComp BK.Close savechanges:=False FName = Dir() Loop End Sub Public Function TotalCodeLinesInVBComponent(VBComp As VBIDE.VBComponent) As Long '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' ' This returns the total number of code lines (excluding blank lines and ' comment lines) in the VBComponent referenced by VBComp. Returns -1 ' if the VBProject is locked. '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''' Dim N As Long Dim S As String Dim LineCount As Long If VBComp.Collection.Parent.Protection = vbext_pp_locked Then TotalCodeLinesInVBComponent = -1 Exit Function End If With VBComp.CodeModule For N = 1 To .CountOfLines S = .Lines(N, 1) If Trim(S) = vbNullString Then ' blank line, skip it ElseIf Left(Trim(S), 1) = "'" Then ' comment line, skip it Else LineCount = LineCount + 1 End If Next N End With TotalCodeLinesInVBComponent = LineCount End Function "John" wrote: I've got a list of approximately 12000 Workbooks. I want to be able to find out which contain macros. Apart from opening each one manually is there a way i can open the Workbook and programatically check to see if a macro exists. I'm not concerned about number of procedures forms, Modules etc, just to see if it contains even a recorded macro. Thanks for any suggestions on this. Regards, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro, how can I check if an opened workbook was changed ? | Excel Programming | |||
Macro in Excel to check if another workbook is opened. | Excel Programming | |||
Before macro runs - check workbook name | Excel Programming | |||
How to check workbook is already opened before passing the workbook obj to a subroutine in Word | Excel Programming | |||
Check if workbook has been changed | Excel Programming |