Failure to set FSO = CreateObject with ExceltoWord! Add-in

Question

I'm using the Excel Add-in ExceltoWord to auto-populate a Word Document from an Excel worksheet. I've followed the instructions from the original developer here.

I'm using the "I created generic bookmark indicators, in Word" and "I put bookmark indicators directly in cells - Left" options with "Delete the Word doc" at the end. When I save settings I get an MS Visual Basic error

Run-time error '429:' ActiveX component can't create object.

I've tried switching different formats of Excel sheet and Word Doc and Word Template as well as leaving the Word Doc closed and opened when saving the configuration.

Public Function validateFileFolderSelection(ByVal fName As String, fType As String, src As String, bFolderOnly As Boolean) As Boolean

'Dim FSO As FileSystemObject 'early binding
Dim FSO As Object 'late binding


    'Set FSO = New FileSystemObject 'early binding
    Set FSO = CreateObject("Scripting.FileSystemObject") 'late binding

    validateFileFolderSelection = True

    'Test for word or excel filename & that the file exists
    If Trim(fName) = vbNullString Then
        validateFileFolderSelection = False
    ElseIf bFolderOnly Then
        If Not FSO.FolderExists(fName) Then
            validateFileFolderSelection = False
        End If
    ElseIf Not FSO.fileExists(fName) Then
            validateFileFolderSelection = False
    End If

End Function

VBA displays an error on Set FSO = CreateObject("Scripting.FileSystemObject") 'late binding.

Answer 1

If you add a Reference to Microsoft Scripting Runtime (VBE > Tools > References...) then enable the "Early Binding" code that you currently have commented out, and your code will work.

To set the reference in the Visual Basic Editor (VBE) go to the Tools menu and select the References... function.

Then from the References dialog that opens, scroll until you locate Microsoft Scripting Runtime, and mark it and then click OK.

In your current code remove the comment marks on the two lines marked as "Early Binding" and apply comment marks on the two lines marked as "Late Binding".

The following is an edit to the original answer because, based on comments, you are continuing to have problems with using FSO (File System Object) code on your system.

Instead of using FSO the following VBA routine will determine if either a specified Directory or File exists. The routine is called "DoesItExist" and I have included an example routine that demonstrates how to call the "DoesItExist" routine.

Sub MyTestRoutine()
    'this first example tests if a specific file exists
    'including a "False" setting for the dirOnly variable is optional
    If DoesItExist("C:\Users\<userID>\Documents\Test\Mydoc.docx") Then
        Debug.Print "File Exists"
    Else
        Debug.Print "File Does Not Exist"
    End If
    'the next example tests if a directory exists,
    'the "True" setting for the dirOnly variable is required for directories
    If DoesItExist("C:\Users\<userID>\Documents\Test", True) Then
        Debug.Print "Directory Exists"
    Else
        Debug.Print "Directory Does Not Exist"
    End If
End Sub

Public Function DoesItExist(ByRef pathName As String, Optional ByRef dirOnly As Boolean) As Boolean
    'this routine checks if a file or folder exists on the system
    'it runs on either a Windows based version of Office or a Mac version
    'if Mac Office then only for the Office 365, 2016, 2019, or later)
    Select Case dirOnly
        Case True
            If Dir(pathName, vbDirectory) = vbNullString Then
                DoesItExist = False
            Else
                DoesItExist = True
            End If
        Case False
            If Dir(pathName, vbNormal) = vbNullString Then
                DoesItExist = False
            Else
                DoesItExist = True
            End If
    End Select
End Function