I wrote this for a member that needed to modify macro code that
referenced a server that was to be retired to another server's
name.
What it will do is basically open an Excel workbook and test for a
password on the vba project. Then it will loop through the
classes & modules looking through every line of code for the old
server name. Then it will replace the old server name with the
new server name and save the workbook and close it.
Access to Visual Basic Projects needs to be manually enabled for
this to work. So there is no threat of malicious action by this code.
VB Code:
Option Explicit
'Add reference to MS Excel xx.0 Object Library
'Trust Access to VB Projects needs to be enabled in Excel first.
'Check it, in Tools > Macros > Security > Trusted Publishers tab - "Trust access to Visual Basic projects"
Private moApp As Excel.Application
Private Sub Command1_Click()
On Error GoTo No_Bugs
Dim oWB As Excel.Workbook
Dim i As Integer
Dim iReplace As Integer
Dim sReplace As String
Dim sOriginalLine As String
Dim iLine As Integer
moApp.Visible = True
'Do a loop here and iterate through your folder containing the workbooks.
'ToDo: Start workbook loop of all folders and sub folders passing the workbook name
Set oWB = moApp.Workbooks.Open("C:\Documents and Settings\VB-Guru\My Documents\Book2.xls")
If moApp.VBE.ActiveVBProject.VBComponents.Item(1).Properties("HasPassword").Value = False Then
If moApp.VBE.ActiveVBProject.VBComponents.Count > 0 Then 'Components are like sheet1,thisworkbook,etc.
For i = 1 To moApp.VBE.ActiveVBProject.VBComponents.Count
Hi there Rob,
I need to to show all the macros that a word document contains, so that a user can run the selected macro through my application. Fortunately found your post near morning .
2 questions please:
1. I have got 2 macros as you can see here:
Line 1: Sub TestMacro()
Line 12: Sub Macro1()
Now, should I extract the macro name with some custom logic or is there any standard way to iterate through the macro collection?
2. Do you have any idea of the exception (between try-catch) ?
VB Code:
int nCount = m_appWord.VBE.ActiveVBProject.VBComponents.Count;
OUTPUT!!!
Project Count: 2
------------------------------------------------------------------
ThisDocument: Type - vbext_ct_Document Line Count: 23
Line 1: Sub TestMacro()
Line 2: '
Line 3: ' TestMacro Macro
Line 4: ' Macro recorded 1/20/2006 by Administrator
Line 5: '
Line 6: Selection.TypeText Text:="Allah"
Line 7: Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
Line 8: Selection.Font.Italic = wdToggle
Line 9: Selection.Font.Bold = wdToggle
Line 10: Selection.Font.Size = 36
Line 11: End Sub
Line 12: Sub Macro1()
Line 13: '
Line 14: ' Macro1 Macro
Line 15: ' Macro recorded 1/20/2006 by Administrator
Line 16: '
Line 17: Selection.MoveRight Unit:=wdCharacter, Count:=1
Line 18: Selection.TypeParagraph
Line 19: Selection.TypeText Text:="o akbar"
Line 20: Selection.MoveLeft Unit:=wdCharacter, Count:=5
Line 21: Selection.TypeParagraph
Line 22: Selection.MoveUp Unit:=wdLine, Count:=1
Line 23: End Sub
------------------------------------------------------------------
NewMacros: Type - vbext_ct_StdModule
I couldnt find anything on your C# Try Catch error but to list all procedures of a module I can do. Its the .ProcOfLine function that compares the lines and can return the type of procedure line it is (Property, Function, Sub, etc). No other way to count or retrieve them then to loop through all lines.
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Reply With Quote
C#: List down Macros from a Word Document
.