CODEWITHSUNDEEP
excelvbavbe
Spencer
Spencer

Reputation: 1

Replace text in multiple workbooks

I am a C++ developer trying to convert something to VBA to replace a call to a legacy ActiveX control.

We have over 100 spreadsheets that rely on an ActiveX control written about 20 years ago. The call into this ActiveX control is creating a strange failure on some Laptops and crashing the code. Research suggests that ActiveX is best replaced. Having re-written the code into a .Bas file I have the problem of applying it to all the legacy spreadsheets.

I am attempting to automate the following:

  1. Open the spreadsheet
  2. Remove "UserForm1" - this is where the ActiveX instantiates and seems better to remove the form
  3. Import the file "Dongle.bas"
  4. Replace the line of code UserForm1mpg1prefectdongle1.CheckSTCLDongle(9, True)" with "Dongle.CheckDongle()". The offending line of code is in the object "ThisWorkbook"
  5. Close the worksheet but add a password to protect the VBA code

Note - in some of the spreadsheets there is a password but I do know the password.

I attempted to record a Macro to at least be able to automate the steps once a spreadsheet is opened but I cannot find a way to replace the code snippet. I have found a command to import the .bas file but when I run the macro it did not seem to import it to the spreadsheet I have open.

Upvotes: 0

Views: 132

Answers (1)

hennep
hennep

Reputation: 660

If you can manually unprotect all workbooks, this code will change the contents of the module, import the dongle code and remove the userform. Instead of "ActiveWorkbook", use the object variable of the opened workbook.

Before you open a number of workbooks from vba, set calculation to manual to speed up the process: Application.Calculation = xlCalculationManual

Application.EnableEvents = False will also help to avoid running code when you open the workbooks

Option Explicit

Sub RemoveUserForm1()
    ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("UserForm1")
End Sub

Sub ImportBasModule()
    ' right click the module and choose export to save it as Dongle.bas
    ActiveWorkbook.VBProject.VBComponents.Import "C:\temp\Dongle.bas"
End Sub

Sub ModifyThisWorkbook()
    Dim i As Integer, arr() As String, sTemp As String, iLine As Integer
    With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook")
        arr = Split(.CodeModule.Lines(1, .CodeModule.CountOfLines), vbCrLf)
        For i = LBound(arr) To UBound(arr)
            Debug.Print arr(i)
            If InStr(arr(i), "UserForm1mpg1prefectdongle1.CheckSTCLDongle(9, True)") > 0 Then
               arr(i) = Replace(arr(i), "UserForm1mpg1prefectdongle1.CheckSTCLDongle(9, True)", "Dongle.CheckDongle()")
            End If
        Next
        .CodeModule.DeleteLines 1, .CodeModule.CountOfLines
        iLine = 1
        For i = LBound(arr) To UBound(arr)
            .CodeModule.InsertLines iLine, arr(i)
            iLine = iLine + 1
        Next
    End With
End Sub

Upvotes: 0

Related Questions