Excel VBProject: replace text strings in VBA Editor

robertocm · December 9, 2018

I'm using this for replacing text strings in the VBProject of all excel files in a folder and subfolders.

I have the same text string in several lines and those lines could have some differences between files: then not feasible for .ReplaceLine method

I'm not interested in placing all the code in a xla AddIn, because the code is similar but not exactly the same in all the files.

Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase

#include <File.au3>
#include <WinAPIFiles.au3>
#include <Excel.au3>

$oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

Global Const $sMessage = "Select Folder"
Global $sFileSelectFolder = FileSelectFolder($sMessage, "")
If @error Then
   MsgBox(0, "", "No folder was selected.")
   Exit
EndIf

Global $bFileOpen

;Look for excel files in selected directory and all subdirectories
Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH)
If Not @error Then

   Local $oAppl = _Excel_Open(Default, Default, False, Default, True)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   For $i = 1 To $aFileList[0]
      $bFileOpen = _WinAPI_FileInUse($aFileList[$i])
      If $bFileOpen = 0 Then
         ;ShellExecute($aFileList[$i])
         Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i])
         ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

         Local $oProject = $oWorkbook.VBProject

         ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
         ;2.7.2 Macromodule delete
         With $oProject
            .VBComponents.Remove(.VBComponents("SplashText"))
            If $iEventError Then
                Consolewrite("SplashText Form not found:  " & $aFileList[$i] & @CRLF)
                $iEventError = 0 ; Reset after displaying a COM Error occurred
            EndIf
            .VBComponents.Import("C:\Documents and Settings\XP\Escritorio\PLANTILLAS_EXPORT\SplashText.frm")
         EndWith

         ;3.2.1.8 Macro: delete
         With $oProject.VBComponents("Actual").CodeModule
            ;3.2.1.2 Macro: find
            If .Find("Sub Check_NumPed(", 1, 1, -1, -1) Then
               ;Note: using '+ 1' at the end of the line because i'm used to add an empty line between procedures (see vba help for ProcCountLines)
               .DeleteLines( .ProcStartLine("Check_NumPed", 0), .ProcCountLines("Check_NumPed", 0) + 1)
            EndIf
         EndWith

         ;Check if range name exists. If not create named ranges
         If Not IsObj($oWorkbook.Sheets("DATOS").Evaluate("Booking_DestPort")) Then
         ;If Not IsObj($oWorkbook.Sheets("DATOS").Range("Booking_DestPort")) Then
            If $oWorkbook.Sheets("DATOS").Range("AC7").value = "DestPort" Then
               $oWorkbook.Names.Add("Booking_DestPort", "=DATOS!$AC$8")
            Else
               ConsoleWrite("->    Not: 'DestPort' in AC7" & @TAB & $aFileList[$i] & @CRLF)
            EndIf
            If $oWorkbook.Sheets("DATOS").Range("AD7").value = "FinalDest" Then
               $oWorkbook.Names.Add("Booking_FinalDest", "=DATOS!$AD$8")
            Else
               ConsoleWrite("->    Not: 'FinalDest' in AD7" & @TAB & $aFileList[$i] & @CRLF)
            EndIf
         EndIf

         ;Open VBE Editor (like Alt+F11)
         $oAppl.VBE.MainWindow.Visible = True
         ;$oAppl.VBE.Windows("Inmediato").Visible = True
         ;https://www.autoitscript.com/forum/topic/77545-resolved-vbaofficeexcel-experts/
         ;Spiff59, Aug 2008
         ;Local $oModules = $oProject.VBComponents
         ;Local $oModules = $oWorkbook.VBProject.VBComponents
         ;$oModules.Item(1).CodeModule.CodePane.Show
         ;$oModules.Item(1).Activate
         ;   With $oModules.Item($y).CodeModule
         ;       .ReplaceLine (1 , "Sub SpellCheck()")
         ;       .DeleteLines (10, 1)
         ;       .InsertLines (7 , "TEST")
         ;   EndWith

         ;Wait 30 seconds for the window to appear.
         Local $hWnd = WinWait("Microsoft Visual Basic - ", "Proyecto - VBAProjec", 30)
         WinActivate($hWnd)
         WinWaitActive($hWnd, "", 30)
         If WinActive($hWnd, "") Then
            ;Sleep(100)
            ;Send("{F7}")

            $oProject.VBComponents("Actual").Activate
            ;Wait 30 seconds for the window to appear.
            Local $hWnd2 = WinWait(" - [Actual (Código)]", "Proyecto - VBAProject", 30)
            WinActivate($hWnd2)
            WinWaitActive($hWnd2, "", 30)

            ;First Replace
            If WinActive($hWnd2, "") Then
               Send("{CTRLDOWN}h{CTRLUP}")
               ;Wait 30 seconds for the window to appear.
               Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30)
               WinActivate($hWnd3)
               WinWaitActive($hWnd3, "", 30)
               Sleep(100)
               Send('Sheets("DATOS").Range("AC8")')
               Sleep(200)
               Send("{TAB}")
               Sleep(100)
               ;Send("{DEL}")
               Send('Range("Booking_DestPort")')
               Sleep(200)
               ControlClick("Reemplazar", "", "[ID:4892]")
               Sleep(100)
               Send("{ALTDOWN}z{ALTUP}")
               Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2)
               ;WinActivate($hWnd4)
               ;WinWaitActive($hWnd4, "", 3)
               If WinActive($hWnd4, "") Then
                  Sleep(100)
                  Send("{SPACE}")
               Else
                  Consolewrite("Not found 1:  " & $aFileList[$i] & @CRLF)
                  Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0)
                  ;WinActivate($hWnd5)
                  ;WinWaitActive($hWnd5, "", 2)
                  Sleep(100)
                  Send("{SPACE}")
               EndIf
               Sleep(100)
               If WinActive($hWnd3, "") Then
                  ;Alt+F4
                  Send("!{F4}")
                  Sleep(100)
               EndIf
            EndIf

            ;Second Replace
            If WinActive($hWnd2, "") Then
               Send("{CTRLDOWN}h{CTRLUP}")
               ;Wait 30 seconds for the window to appear.
               Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30)
               WinActivate($hWnd3)
               WinWaitActive($hWnd3, "", 30)
               Sleep(100)
               Send('Sheets("DATOS").Range("AD8")')
               Sleep(200)
               Send("{TAB}")
               Sleep(100)
               Send('Range("Booking_FinalDest")')
               Sleep(200)
               ControlClick("Reemplazar", "", "[ID:4892]")
               Sleep(100)
               Send("{ALTDOWN}z{ALTUP}")
               Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2)
               ;WinActivate($hWnd4)
               ;WinWaitActive($hWnd4, "", 3)
               If WinActive($hWnd4, "") Then
                  Sleep(100)
                  Send("{SPACE}")
               Else
                  Consolewrite("Not found 2:  " & $aFileList[$i] & @CRLF)
                  Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0)
                  ;WinActivate($hWnd5)
                  ;WinWaitActive($hWnd5, "", 2)
                  Sleep(100)
                  Send("{SPACE}")
               EndIf
               Sleep(100)
               If WinActive($hWnd3, "") Then
                  ;Alt+F4
                  Send("!{F4}")
                  Sleep(100)
               EndIf
            EndIf

            ;Close VBE Editor
            If WinActive($hWnd2, "") Then
               $oAppl.VBE.ActiveWindow.Close
               ;Send("^{F4}")
               ;Sleep(100)
               ;Send("^s")

               $oAppl.VBE.MainWindow.Visible = False
               ;Sleep(100)
               ;Send("!{F4}")
            EndIf
         EndIf

         _Excel_BookClose($oWorkbook, True)
         ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)
      EndIf

   Next
Else
   MsgBox(16, "Error", "No files were found in the folder specified.")
EndIf

_Excel_Close($oAppl)
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)


;This is a custom error handler
Func ErrFunc()
   $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

Edited December 9, 2018 by robertocm

robertocm · December 22, 2018

This seems a better option:

Replace search strings in all modules / Replace all code in a module:

;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
Local $Find = "L_x_Bot"
Local $Replace = "L_x_Unid"
;Local $Find2 = "End Function" & @CRLF & @CRLF
;Local $Replace2 = "End Function" & @CRLF
Local $c00 = '', $Found
ConsoleWrite("+-> " & $aFileList[$i] & @CRLF)
For $cp In $oProject.VBcomponents
   With $cp.CodeModule
      If .CountOfLines > 0 Then
         ;3.1.1 Read the complete VBA code
         $c00 = .Lines(1, .CountOfLines)
         If StringInStr($c00, $Find) > 0 Then
            $Found = True
            $c00 = StringReplace($c00, $Find, $Replace, 0)
         EndIf
         ;If StringInStr($c00, $Find2) > 0 Then
            ;$Found = True
            ;$c00 = StringReplace($c00, $Find2, $Replace2, 0)
         ;EndIf
         If $Found Then
            $Found = False
            ;3.1.3 Delete all code from a codemodule
            .DeleteLines(1, .CountOfLines)
            ;3.2.1.3 Macro: add
            .AddFromString($c00)
            Consolewrite("--> String Replaced in: " & $cp.Name & @CRLF)
         ;Else
            ;Consolewrite(@TAB & "Not Found in: " & $cp.Name & @CRLF)
         EndIf
      EndIf
   EndWith
Next

Related: Replace search strings in a macro. Note: 4th parameter of StringReplace function set to 0

;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
Local $c00 = ''
Local $Module = 'Actual'
Local $Name = 'My_Sub_Test'
Local $Find = 'InAr = Application.Transpose(Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")))'
Local $Replace = 'InAr = Application.Transpose(.Range(.Cells(FirstRow, "A"), .Cells(LastRow, "A")))'
If IsObj($oProject.VBComponents($Module).CodeModule) Then
   With $oProject.VBComponents($Module).CodeModule
      $c00 = .Lines(.ProcStartLine($Name, 0) + 1, .ProcCountLines($Name, 0) - 2)
      $c00 = StringReplace($c00, $Find, $Replace, 0)
      .DeleteLines(.ProcStartLine($Name, 0) + 1, .ProcCountLines($Name, 0) - 2)
      .InsertLines(.ProcStartLine($Name, 0) + 1, $c00)
   EndWith
Else
   ConsoleWrite("Error Module " & $Module & " Not found: " & @TAB & $aFileList[$i] & @CRLF)
   $iEventError = 0 ; Reset after displaying a COM Error occurred
EndIf

Related: Replace lines in a macro

;Replace lines in a macro
;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
;3.2.1.6 Macro: replace code
If IsObj($oProject.VBComponents("Hoja1").CodeModule) Then
   Local $c00 = ''
   With $oProject.VBComponents("Hoja1").CodeModule
      ;Note: using '+ 2' & '-3' because i'm used to add an empty line between procedures (see vba help for ProcCountLines)
      ;$c00 = .Lines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 2, .ProcCountLines("Worksheet_BeforeRightClick", 0) - 3)
      ;>>>> pending test: with no separation line before procedure start line
      $c00 = .Lines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 1, .ProcCountLines("Worksheet_BeforeRightClick", 0) - 2)
      If StringInStr($c00, "Application.Intersect(Target, Me.ListObjects(""Lines_TB"").ListColumns(8)") > 0 Then
         $c00 = StringReplace($c00, "If Not Application.Intersect(Target, Me.ListObjects(""Lines_TB"").ListColumns(8).DataBodyRange) Is Nothing Then" & @CRLF & _
            "    Ir_a_precio" & @CRLF & _
            "    CANCEL = True" & @CRLF & _
            "End If" & @CRLF & _
            "" & @CRLF, '', 1)

         ;.DeleteLines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 2, .ProcCountLines("Worksheet_BeforeRightClick", 0) - 3)
         ;with no separation line before procedure start line
         .DeleteLines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 1, .ProcCountLines("Worksheet_BeforeRightClick", 0) - 2)
         .InsertLines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 2, $c00)
      EndIf
   EndWith
Else
   ConsoleWrite("Error Hoja1 Not found: " & @TAB & $aFileList[$i] & @CRLF)
   $iEventError = 0 ; Reset after displaying a COM Error occurred
EndIf
;~ ;Replace lines in a macro (first procedure in module)
;~ ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
;~ Local $c00 = ''
;~ If IsObj($oProject.VBComponents("Hoja1").CodeModule) Then
;~    With $oProject.VBComponents("Hoja1").CodeModule
;~    ;Note: First procedure in module, starting in first line: then +1, -2
;~    $c00 = .Lines(.ProcStartLine("Worksheet_Change", 0) + 1, .ProcCountLines("Worksheet_Change", 0) - 2)
;~    If StringInStr($c00, "        If sKey = """" Then") > 0 Then
;~       $c00 = StringReplace($c00, "        If sKey = """" Then Exit Sub", _
;~          "        If sKey = """" Then" & @CRLF & _
;~          "            Target.Resize(1, 8).ClearContents" & @CRLF & _
;~          "            GoTo CleanUp" & @CRLF & _
;~          "        End If", 1)

;~       ;Note: First procedure in module, starting in first line: then +1, -2
;~       .DeleteLines(.ProcStartLine("Worksheet_Change", 0) + 1, .ProcCountLines("Worksheet_Change", 0) - 2)
;~       .InsertLines(.ProcStartLine("Worksheet_Change", 0) + 1, $c00)
;~    EndIf
;~    EndWith
;~ Else
;~    ConsoleWrite("Error Module Hoja1 Not found: " & @TAB & $aFileList[$i] & @CRLF)
;~    $iEventError = 0 ; Reset after displaying a COM Error occurred
;~ EndIf

Related: Replace all code in a macro

;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
;3.2.1.6 Macro: replace code
Local $c00 = ''
If IsObj($oProject.VBComponents("Hoja1").CodeModule) Then
   With $oProject.VBComponents("Hoja1").CodeModule
      ;Note: using '+ 2' & '-3' because i'm used to add an empty line between procedures (see vba help for ProcCountLines)
      $c00 = "If Target.Cells.Count > 1 Then Exit Sub" & @CRLF & _
      "On Error GoTo ExceptionHandling" & @CRLF & _
      "" & @CRLF & _
      "If Not Application.Intersect(Target, Me.ListObjects(""Client_TB"").DataBodyRange) Is Nothing Then" & @CRLF & _
      "    CANCEL = True" & @CRLF & _
      "    Target = UCase(Target)" & @CRLF & _
      "End If" & @CRLF & _
      "'...." & @CRLF & _
      "CleanUp:" & @CRLF & _
      "    With Application" & @CRLF & _
      "    .EnableEvents = True" & @CRLF & _
      "    .ScreenUpdating = True" & @CRLF & _
      "    End With" & @CRLF & _
      "    On Error Resume Next" & @CRLF & _
      "    cn.Close" & @CRLF & _
      "    DriveMapDel" & @CRLF & _
      "    Set rst = Nothing" & @CRLF & _
      "    Set cn = Nothing" & @CRLF & _
      "    Set tbl = Nothing" & @CRLF & _
      "    Exit Sub" & @CRLF & _
      "ExceptionHandling:" & @CRLF & _
      "    MsgBox ""Error: "" & Err.Description" & @CRLF & _
      "    Resume CleanUp"

      .DeleteLines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 2, .ProcCountLines("Worksheet_BeforeRightClick", 0) - 3)
      .InsertLines(.ProcStartLine("Worksheet_BeforeRightClick", 0) + 2, $c00)
   EndWith
EndIf

Replace text in declarations section of a module

;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
;3.2.1.6 Macro: replace code
If IsObj($oProject.VBComponents("Actual").CodeModule) Then
   Local $c03 = ''
   With $oProject.VBComponents("Actual").CodeModule
      $c03 = .Lines(1, .CountOfDeclarationLines)
      If StringInStr($c03, " Ruta As String,") > 0 Then
         $c03 = StringReplace($c03, " Ruta As String,", '', 1)
         .DeleteLines(1, .CountOfDeclarationLines)
         .AddFromString($c03)
      Else
         Consolewrite("--> String Not Found:  " & $aFileList[$i] & @CRLF)
      EndIf
   EndWith
Else
   ConsoleWrite("Error Module Actual Not found: " & @TAB & $aFileList[$i] & @CRLF)
   $iEventError = 0 ; Reset after displaying a COM Error occurred
EndIf

Delete empty line after declaration lines in all modules

;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html
Local $c00 = '', $DecLines
ConsoleWrite(@TAB & $aFileList[$i] & @CRLF)
For $cp In $oProject.VBcomponents
   $c00 = 'dummy'
   With $cp.CodeModule
      $DecLines = .CountOfDeclarationLines + 1
      If $DecLines > 0 Then
         $c00 = .Lines($DecLines, 1)
         If $c00 = '' Then
            If .CountOfLines > $DecLines Then
               .DeleteLines($DecLines, 1)
               Consolewrite(@TAB & @TAB & "--> Deleted Empty Line After Declarations: " & $cp.Name & @CRLF)
            EndIf
         ;Else
            ;Consolewrite(@TAB & "Not Found in: " & $cp.Name & @CRLF)
         EndIf
      EndIf
   EndWith
Next

Restore lost print areas (for excel Spanish version)

;...
$oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler

;...
Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i])
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Sleep(2000)

;Restore print areas
Global $Name ='', $Ref = '', $pAreas = 0
For $nm In $oWorkbook.Names
   $Name = $nm.Name
   $Ref = $nm.RefersTo
   ;Deleting names with invalid references
   ;http://www.datawright.com.au/excel_resources/excel_range_names_vba.htm
   If StringInStr($Ref, "#¡REF") > 0 Then
      Consolewrite("--> Deleting: " & $Name & @TAB & $aFileList[$i] & @CRLF)
      $nm.Delete
      If $iEventError Then
          Consolewrite("Error in Deleting Names with invalid references: " & $Name & @TAB & $aFileList[$i] & @CRLF)
          $iEventError = 0 ; Reset after displaying a COM Error occurred
      EndIf
   ElseIf StringInStr($Name, "Print_Area") > 0 Then
      $pAreas += 1
      If StringInStr($Ref, " ") = 0 Then
         $oWorkbook.Sheets(StringSplit($Name, "!")[1]).Names.Add("Área_de_impresión", $Ref)
      Else
         $oWorkbook.Sheets(StringSplit(StringReplace($Name, "'", ""), "!")[1]).Names.Add("Área_de_impresión", $Ref)
      EndIf
      ;Consolewrite(@TAB & "--> Deleting:  " & $Name & @TAB & $aFileList[$i] & @CRLF)
      If $iEventError Then
          Consolewrite(@TAB & "Error creating 'Área_de_impresión' " & $Ref & "  " & $aFileList[$i] & @CRLF)
          $iEventError = 0 ; Reset after displaying a COM Error occurred
      EndIf
      $nm.Delete
      If $iEventError Then
          Consolewrite(@TAB & "Error in Deleting Print_Area Names:  " & $aFileList[$i] & @CRLF)
          $iEventError = 0 ; Reset after displaying a COM Error occurred
      EndIf
   EndIf
Next
If $pAreas = 0 Then Consolewrite("Error Lost All Print Areas:  " & $aFileList[$i] & @CRLF)
;~ ;Restore print areas: another option, but slower
;~ ;Check if range name exists
;~ Global $ShName = '', $Sheets = $oWorkbook.Sheets
;~ For $ws In $Sheets
;~    $ShName = $ws.Name
;~    If Not IsObj($oWorkbook.Sheets($ShName).Evaluate("Área_de_impresión")) Then
;~    ;If Not IsObj($oWorkbook.Sheets($ShName).Range("Área_de_impresión")) Then
;~    If IsObj($oWorkbook.Sheets($ShName).Evaluate("Print_Area")) Then
;~        $oWorkbook.Sheets($ShName).Names.Add("Área_de_impresión", "=" & $oWorkbook.Sheets($ShName).Range("Print_Area").Address)
;~        $oWorkbook.Sheets($ShName).Names.Add("Print_Area", "")
;~        ;$oWorkbook.Sheets($ShName).Names("Print_Area").Delete
;~     EndIf
;~  EndIf
;~ Next


;...
;This is a custom error handler
Func ErrFunc()
   $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

Compare lines of the same macro between files

Local $c00 = '', $ProcStart
Local $Module = "Actual"
Local $Name = "Formulario_Lotes"
Local $sCheck = "On Error GoTo ExceptionHandling" & @CRLF & _
   "With Application" & @CRLF & _
   "  '.EnableEvents = False" & @CRLF & _
   "  .ScreenUpdating = False" & @CRLF & _
   "End With" & @CRLF & _
   "" & @CRLF & _
   "Dim cnStr As String, RutaTemp As String, sSQL As String, SubSQL As String, CodProd As String" & @CRLF & _
   "CodProd = ActiveCell.Offset(0, 1).Value" & @CRLF & _
   " Resume CleanUp"

If IsObj($oProject.VBComponents($Module).CodeModule) Then
   With $oProject.VBComponents($Module).CodeModule
      $ProcStart = .ProcStartLine($Name, 0)
      $c00 = .Lines($ProcStart + 1, .ProcCountLines($Name, 0) - 2)
      ;ConsoleWrite(.Lines($ProcStart + 1, 1) & @CRLF)
      ;ConsoleWrite(.Lines($ProcStart + .ProcCountLines($Name, 0) - 2, 1) & @CRLF)
      Local $iCmp = StringCompare($sCheck, $c00)
      If $iCmp <> 0 Then
         ConsoleWrite("--> Dif. Macro: " & $aFileList[$i] & @CRLF)
         Local $aCheck = StringSplit($sCheck, @CRLF, 1)
         Local $ac00 = StringSplit($c00, @CRLF, 1)
         If $iCmp > 0 Then
            For $j = 1 To $aCheck[0]
               If $j <= $ac00[0] Then
                  If StringCompare($aCheck[$j], $ac00[$j]) <> 0 Then ConsoleWrite(@TAB & "2nd Lin " & StringFormat($j + $ProcStart, "%3s") & ": " & $ac00[$j] & @CRLF)
               Else
                  ConsoleWrite(@TAB & "1st Lin " & StringFormat($j + $ProcStart, "%3s") & ": " & $aCheck[$j] & @CRLF)
               EndIf
            Next
         Else
            For $j = 1 To $ac00[0]
               If $j <= $aCheck[0] Then
                  If StringCompare($aCheck[$j], $ac00[$j]) <> 0 Then ConsoleWrite(@TAB & "2nd Lin " & StringFormat($j + $ProcStart, "%3s") & ": " & $ac00[$j] & @CRLF)
               Else
                  ConsoleWrite(@TAB & "2nd Lin " & StringFormat($j + $ProcStart, "%3s") & ": " & $ac00[$j] & @CRLF)
               EndIf
            Next
         EndIf
      EndIf
   EndWith
Else
   ConsoleWrite("Error Module " & $Module & " Not found: " & @TAB & $aFileList[$i] & @CRLF)
   $iEventError = 0 ; Reset after displaying a COM Error occurred
EndIf

Edited May 12, 2019 by robertocm
restore lost print areas example

Sign In

Excel VBProject: replace text strings in VBA Editor

Recommended Posts

robertocm

robertocm

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content

_Excel_RangeFind() and empty search

Loading Large Table for lookup

Error when run as a admin, Error on a line of code that does not exist (Error: Variable must be of type "Object") - (Moved)

aArray headers Always transferred to existing Excel spreadsheet.

Seperating filename into coumns in Excel

Browse

AutoIt Resources

Release

Beta