how to tell if excel file contains vba code or macro

[suj_78]

Hi Guys,

I am looking for a script or some code in excel or c# to go through set of excel files on a server and find out the files which contains macro or vba code. How to achive this?

Your help will be appreciated.

thank you.

 

[mikerickson]

Quick thought. Loop through the files, opening and then closing each file. If the "Enable macros?" prompt comes up.. that file contains a macro.

Another thought, check all the VBComponents.CodeModule for .CountOfLines > 0.

 

[erik.van.geit]

Hello, suj_78,
WELCOME to the Board!!!!!

To my sense Mikes second suggestion will not help. If the project is locked it seems that it will not be found. So I think VBA will not help, at least I can not find a solution but will call some MVPs.

Sub test()
'if FALSE then 2 possibilities
'1. no project
'2. locked project
MsgBox HasProject(Workbooks("test.xls"))
End Sub
 
Function HasProject(WB As Workbook) As Boolean
Dim WbProjComp As Object
On Error Resume Next
Set WbProjComp = WB.VBProject.VBComponents
If Not WbProjComp Is Nothing Then HasProject = True
End Function

kind regards,
Erik

 

[suj_78]

Hi Erik,

Do you know any MVP who can guide me with an example using Microsoft.Office.Interop.Excel in .net to find the excel application with VBA code, that would be very useful.

Many Thanks guys.

Thanks to Mick as well for his time.

 

[erik.van.geit]

I left a note to the Mr Excel MVPs.

 

[suj_78]

Hi Erik,
I have found a solution, using interop application object, we can check if the workbook has VBproject associated with it or not. The property is called HASVBproject, it would be nice to know the answer of the MVP as well and do they have any comments on my findings.
Thanks

 

[erik.van.geit]

Hi Erik,
I have found a solution, using interop application object, we can check if the workbook has VBproject associated with it or not. The property is called HASVBproject, it would be nice to know the answer of the MVP as well and do they have any comments on my findings.
Thanks

I can not force any MVP to come here.
Can you tell what "interop application object" is? Or is there a typo "interop"?

You made me think again about the problem... It was simple, I must have been too tired yesterday

Sub test()
MsgBox HasProject(Workbooks("Book1.xls"))
End Sub
 
Function HasProject(WB As Workbook) As Boolean
Dim WbProjComp As String
 
On Error Resume Next
WbProjComp = WB.VBProject.Name
If Len(WbProjComp) > 0 Then HasProject = True
End Function

Of course, the name of the project is always visible, even when it is locked.

This is still not what you need: check on the internet for projects.
Can you provide the URLs of some workbooks to test? (I do not promise any results, but will try)

best regards,
Erik

 

[suj_78]

Ok, i didn't mean to bring MVP's overe here but to get their comments on what i have solved makes sense to them or not afterall Microsoft has honored them with the title.

Interop is an assembly in .net framework. The primary function of interop as the name suggests is to provide interaction between manged code(CLR compliant code) with non manged code called windows API. If you think Excel from the point of view of managed and non managed world, it belongs to non managed world by being part of Microsoft Office Suite. .Net framweork provides a whole suite of interops for different types of office application and one of them is Microsoft.Office.Interop.Excel. This assembly has got plethora of objects and members that you can play with to manipulate properties, behaviour and events belonging to excel application.

Hope the above paragraph gives you some idea if not all. Let me know if you need more info.

Once again, thanks for your time on this.

 

[erik.van.geit]

This is not my stuff anymore
But can you post the solution?