Firstly fileName will never be null. You should be testing for it being equal to "" or vbNullString
The error means that there is no vbcomponent with the specified name. Once again, your IsNull function will not handle that. You either need an error handler, or to simply loop through all the vbcomponents and test each one's name.
For example:
Sub DeleteVBComponent(ByVal wb As Workbook, ByVal CompName As String)
On Error Resume Next
'Delete the component
wb.VBProject.VBComponents.Remove wb.VBProject.VBComponents(CompName) '***THIS LINE THROWS RUNTIME ERROR 9***
End Sub
and
Sub AutoUpdateBasFiles()
'Automate import of all other modules
Dim userID As String
userID = Environ("UserName")
Dim destPath As String
destPath = "C:\Users" & userID & "\AppData\Roaming\Microsoft\Excel\XLSTART"
Dim sourcePath As String
sourcePath = "C:\File Path" '**FilePath removed for the purpose of this post
'Loop through all files in a folder
Dim fileName As Variant
fileName = Dir(sourcePath)
Dim TargetWB As Workbook
Set TargetWB = Workbooks("PERSONAL.xlsb")
Dim fileNameNoExt As String
While fileName <> ""
fileNameNoExt = LCase$Left(fileName, InStrRev(fileName, ".") - 1))
for each vbc in TargetWB.VBProject.VBComponents
If LCase$(vbc.name) = fileNameNoExt Then 'Check for existence of module
Call DeleteVBComponent(Workbooks("PERSONAL.xlsb"), fileNameNoExt) 'Delete existing module before importing the new one of the same name
Exit For
End If
Next vbc
TargetWB.VBProject.VBComponents.Import (sourcePath & fileName) 'Import the new module
'Set the fileName to the next file
fileName = Dir()
Wend
'Save the workbook
Workbooks("PERSONAL.xlsb").Save
End Sub
Bookmarks