On 2002-05-17 00:54, murtuza wrote:
Hello,
I want a list of Macros associated with an Excel Workbook by creating an object of Excel in VB.
Can anyone please help.
-Murtuza.
Not tested But;
This example may help you out.
I gather you have the references set up
in your VB Project.
<pre/>
Sub Commandbutton()
Dim xlApp As Object
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim VBComp
Set xlApp = GetObject("C:ExcelFilesUseful###.xls")
' Make it visible...
'xlApp.Visible = True
'// Set references
Dim xlModule As Object
Set xlModule = xlApp.VBProject.VBComponents
For Each VBComp In xlModule
Set VBCodeMod = xlApp.VBProject.VBComponents(VBComp.Name).CodeModule
If VBComp.Type = 1 Or VBComp.Type = 100 Then
With VBCodeMod
StartLine = .CountOfDeclarationLines + 1
Do Until StartLine >= .CountOfLines
MsgBox .ProcOfLine(StartLine, vbext_pk_Proc)
StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
End If
Set VBCodeMod = Nothing
Next VBComp
' Remember to release module
Set xlModule = Nothing
Set xlApp = Nothing
' Clean up
End Sub
</pre>