(Solved) – .bas import Runtime Error 80070006 The handle is invalid

(solved)-–.bas-import-runtime-error-80070006-the-handle-is-invalid

I am getting a windows system error when attempting to import a .bas file as a Module in a VBA project. I am able to import manually, and I am able to programmatically import by name, but when I put it into a loop with different import handling conditions, I am getting the error in the picture below.

enter image description here

Here is an example of my test code to verify that my methods work the way I suspect. This version succeeds:

Sub test()

Dim thisBook As String
Dim filePath As String
filePath = "O:Quality RepositoriesProcess Checks Workbook Repository"
thisBook = "Process Control Workbook.xlsm"

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

End Sub

And here is the dysfunctional version that handles all the other tile types I might need to import. It succeeds in overwriting all the event handling on the Sheet and Workbook objects, but stops with a system error on the first .frm import or .bas import.

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 RepositoriesProcess Checks Workbook Repository"
thisBook = "Process Control Workbook.xlsm"

For Each element In Workbooks(thisBook).VBProject.VBComponents
    If element.Type = 1 Then 'Modules
        If Dir(filePath & element.Name & ".bas") <> "" Then
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.import _
                (filePath & element.Name & ".bas")
            MsgBox (element.Name & ".bas imported")
        End If
    ElseIf element.Type = 3 Then 'Forms
        If Dir(filePath & element.Name & ".frm") <> "" Then
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.import _
                (filePath & element.Name & ".frm")
            MsgBox (element.Name & ".frm imported")
        End If
    ElseIf element.Type = 2 Then 'Class Modules
        If Dir(filePath & element.Name & "cls") <> "" Then
            Workbooks(thisBook).VBProject.VBComponents.Remove element
            Workbooks(thisBook).VBProject.VBComponents.import _
                (filePath & element.Name & ".cls")
            MsgBox (element.Name & ".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

The forum posts I have seen with this error usually pertain to websites and coding languages I don't use. I hope someone can offer some suggestions on why one version fails as opposed to the other. As far as I can tell, they are functionally equivalent. I would be very happy to have suggestions on how I can avoid this error and bonus points if someone can explain to me what a handle is.

Just in case it matters, here is a list of the files it searches for. I noticed that the order it searches for them in didn't make sense to me, so I thought it might be valuable information.

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *