バージョン管理-ランタイムエラー80070006ハンドルが無効です

After methodically changing each line one at a time and running the short version of the macro, I was able to identify the source of the issue. I can't explain what causes "The handle is invalid" errors, but I can explain what solved the problem for me:

As the macro executes, if the "element" object it is attempting to remove is not currently executing, the method succeeds in removing that element mid execution. Any line thereafter that refers to "element" will then be invalid because the object no longer exists within the project.

I noticed this because I tested the following code. The change is that I imported the file by name, but then simply tried to create a message box using element.Name. which resulted in highlighting the MsgBox line with an "invalid handle" error:

For Each element In Workbooks(thisBook).VBProject.VBComponents
    If element.Name = "OutsourceModule" Then
        If Dir(filePath & element.Name & ".bas") <> "" Then
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.Import _
                (filePath & "OutsourceModule" & ".bas")
            MsgBox (element.Name & ".bas imported")
        End If
    End If
Next element

That told me that the name itself has to be invalid after removing the element. I addressed the error by creating a String Variable to store the name of the element after removing the element. This allowed me to pass that same name to the import method and again to the message box function!

The following is a perfectly successful version of Version Control for an Excel Workbook. It uses the names of current objects in the project to search a specified file location for exported files of the same name. If it finds matches in that location, then it replaces forms, class modules, and modules with imported versions from the location. If it finds .cls files (exported from sheet and workbook objects) then it deletes the text in those objects, and replaces it with the text of the files with the same names.

Sub import_mods()

Dim filePath As String
Dim thisBook As String
Dim fso As FileSystemObject
Dim ts As TextStream
Dim S As String
filePath = "O:\Quality Repositories\Process Checks Workbook Repository\"
thisBook = "Process Control Workbook.xlsm"

Dim newString As String

For Each element In Workbooks(thisBook).VBProject.VBComponents
    If element.Type = 1 Then 'Modules
        If Dir(filePath & element.Name & ".bas") <> "" Then
            newString = element.Name
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.import _
                (filePath & newString & ".bas")
            MsgBox (newString & ".bas imported")
        End If
    ElseIf element.Type = 3 Then 'Forms
        If Dir(filePath & element.Name & ".frm") <> "" Then
            newString = element.Name
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.import _
                (filePath & newString & ".frm")
            MsgBox (newString & ".frm imported")
        End If
    ElseIf element.Type = 2 Then 'Class Modules
        If Dir(filePath & element.Name & "cls") <> "" Then
            newString = element.Name
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.import _
                (filePath & newString & ".cls")
            MsgBox (newString & ".cls imported")
        End If
    ElseIf element.Type = 100 Then 'Sheet or Workbook modules
        If Dir(filePath & element.Name & ".cls") <> "" Then
            Set fso = New FileSystemObject
            Set ts = fso.OpenTextFile(filePath & element.Name & ".cls", ForReading)
            Do While Not ts.AtEndOfStream
                If ts.line <= 9 Then
                    ts.SkipLine
                Else
                    S = S & ts.ReadLine & vbCrLf
                End If
            Loop
            With element.CodeModule
                .DeleteLines 1, .CountOfLines
                .InsertLines 1, S
            End With
            MsgBox (element.Name & " imported" & vbCrLf & S)
            S = vbNullString
            ts.Close
        End If
    End If
Next element

End Sub

注意すべき点の1つは、このマクロを格納するモジュールはまだ実行されているため、実行中に削除されないことです。その場合、そのマクロのインポートされたバージョンは、名前がに変更されmoduleName1ます。ブックプロジェクトに個別のイベントまたはサブプロシージャを非常に簡単に作成して、別の時間にそのモジュールの名前を変更できます。

これを行うにはもっとエレガントな方法があると確信していますが、以下は名前の変更の問題をどのように処理したかを示すワークブックイベントです。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

For Each element In Workbooks("Process Control Workbook.xlsm").VBProject.VBComponents

    If element.Type = 1 Then 'Modules
        element.Name = Replace(element.Name, "Module1", "Module")
    ElseIf element.Type = 3 Then 'Forms
        element.Name = Replace(element.Name, "1", "")
    ElseIf element.Type = 2 Then 'Class Modules
        element.Name = Replace(element.Name, "Module1", "Module")
    ElseIf element.Type = 100 Then 'Sheet or Workbook modules
    End If
Next element

End Sub

これが誰かに役立つことを願っています！