Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Way to output list of macros to file
I'm looking for a way to generate a list of all the macros contained within a
workbook. I've found two suggestions online but haven't been able to get either to work. Is anyone aware of an easy to get the list of names? PJZ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Way to output list of macros to file
I went to cpearson website and got this code. You have to change FName in
the code below and do two things in the comments in the code below. 1) Add the reference library 2) change your macro security. Sub DeleteAllVBACode() '1) add following library as reference 'using tools - references 'Microsoft Visual Basic for Applications Extensibility 5.3 '2) 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. Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim ProcKind As VBIDE.vbext_ProcKind Set sht = ThisWorkbook.ActiveSheet FName = "d:\Visual Basic\text.xls" Set bk = Workbooks.Open(Filename:=FName) Set VBProj = bk.VBProject RowCount = 1 For Each VBComp In VBProj.VBComponents Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CountOfDeclarationLines + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Do Until LineNum = .CountOfLines sht.Range("A" & RowCount) = ProcName sht.Range("B" & RowCount) = ProcKindString(ProcKind) RowCount = RowCount + 1 LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Loop End With Next VBComp bk.Close savechanges:=False End Sub Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String Select Case ProcKind Case vbext_pk_Get ProcKindString = "Property Get" Case vbext_pk_Let ProcKindString = "Property Let" Case vbext_pk_Set ProcKindString = "Property Set" Case vbext_pk_Proc ProcKindString = "Sub Or Function" Case Else ProcKindString = "Unknown Type: " & CStr(ProcKind) End Select End Function "PJ" wrote: I'm looking for a way to generate a list of all the macros contained within a workbook. I've found two suggestions online but haven't been able to get either to work. Is anyone aware of an easy to get the list of names? PJZ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Way to output list of macros to file
Hi Joel,
That looks a little different then the code I already tried so it's worth a shot. I will test it out when I get back to work on Monday. Thank you. "joel" wrote: I went to cpearson website and got this code. You have to change FName in the code below and do two things in the comments in the code below. 1) Add the reference library 2) change your macro security. Sub DeleteAllVBACode() '1) add following library as reference 'using tools - references 'Microsoft Visual Basic for Applications Extensibility 5.3 '2) 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. Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim ProcKind As VBIDE.vbext_ProcKind Set sht = ThisWorkbook.ActiveSheet FName = "d:\Visual Basic\text.xls" Set bk = Workbooks.Open(Filename:=FName) Set VBProj = bk.VBProject RowCount = 1 For Each VBComp In VBProj.VBComponents Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CountOfDeclarationLines + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Do Until LineNum = .CountOfLines sht.Range("A" & RowCount) = ProcName sht.Range("B" & RowCount) = ProcKindString(ProcKind) RowCount = RowCount + 1 LineNum = LineNum + .ProcCountLines(ProcName, ProcKind) + 1 ProcName = .ProcOfLine(LineNum, ProcKind) Loop End With Next VBComp bk.Close savechanges:=False End Sub Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String Select Case ProcKind Case vbext_pk_Get ProcKindString = "Property Get" Case vbext_pk_Let ProcKindString = "Property Let" Case vbext_pk_Set ProcKindString = "Property Set" Case vbext_pk_Proc ProcKindString = "Sub Or Function" Case Else ProcKindString = "Unknown Type: " & CStr(ProcKind) End Select End Function "PJ" wrote: I'm looking for a way to generate a list of all the macros contained within a workbook. I've found two suggestions online but haven't been able to get either to work. Is anyone aware of an easy to get the list of names? PJZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Four macros, one main output | Excel Worksheet Functions | |||
Multiple Macros at once different output | Excel Programming | |||
Open CSV file, format data and write output to a text file. | Excel Programming | |||
Ooh .. Linking a list to a list to an output cell | Excel Discussion (Misc queries) | |||
Can macros output to a cell selected prior to running it? | Excel Worksheet Functions |