locked
Write a macro to change macros RRS feed

  • Question

  • I need to change one part of one line in multiple macros

    I want to write a macro to change part of a line of code in another macro in multiple files for someone else to run at their site.

    I want to open the file, open VBA code (Module 18) and change (find) \1 SQC Review Templates\ (Replace) \2 Bus Unit Response Templates\

    Thursday, December 3, 2015 4:24 AM

Answers

  • for someone else to run at their site.

    That is nothing for beginners and critical, when something goes wrong all files are damaged!

    And the security settings must allow the macro to access the VB project.

    Below is a simple sample for one file.

    Andreas.

    Option Explicit
    
    Sub Test()
      Dim Wb As Workbook
      Dim vbComp As Object 'VBIDE.VBComponent
      Dim Contents As String
      
      'In this workbook...
      Set Wb = ActiveWorkbook
      '...in all components...
      For Each vbComp In Wb.VBProject.VBComponents
        '...access the code module:
        With vbComp.CodeModule
          'Has any content?
          If .CountOfLines > 0 Then
            'Get it
            Contents = .Lines(1, .CountOfLines)
            'Search for the keyword
            If InStr(1, Contents, "Whatever", vbTextCompare) > 0 Then
              'Replace it
              Contents = Replace(Contents, "Whatever", "ThisOne", Compare:=vbTextCompare)
              'Remove the content
              .DeleteLines 1, .CountOfLines
              'Add the modified one
              .InsertLines 1, Contents
            End If
          End If
        End With
      Next
    End Sub

    • Proposed as answer by André Santo Saturday, December 5, 2015 11:14 AM
    • Marked as answer by David_JunFeng Tuesday, December 15, 2015 1:41 AM
    Thursday, December 3, 2015 2:12 PM