run-time error 50035 method 'export' of object '_vbcomponent' failed

[Querious7]

Having trouble in the last part of the following macro whose purpose is to export all the modules in my workbook to a file folder 'VBAProjectFiles' :

Code: Select all

Public Sub ExportModules()
    Dim bExport As Boolean
    Dim wkbSource As Excel.Workbook
    Dim szSourceWorkbook As String
    Dim szExportPath As String
    Dim szFileName As String
    Dim cmpComponent As VBIDE.VBComponent

    ''' The code modules will be exported in a folder named.
    ''' VBAProjectFiles in the Documents folder.
    ''' The code below create this folder if it not exist
    ''' or delete all files in the folder if it exist.
    If FolderWithVBAProjectFiles = "Error" Then
        MsgBox "Export Folder not exist"
        Exit Sub
    End If
   
    On Error Resume Next
        Kill FolderWithVBAProjectFiles & "\*.*"
    On Error GoTo 0

    ''' NOTE: This workbook must be open in Excel.
    szSourceWorkbook = ActiveWorkbook.Name
    Set wkbSource = Application.Workbooks(szSourceWorkbook)
   
    If wkbSource.VBProject.Protection = 1 Then
    MsgBox "The VBA in this workbook is protected," & _
        "not possible to export the code"
    Exit Sub
    End If
   
    szExportPath = FolderWithVBAProjectFiles & "\"
   
    For Each cmpComponent In wkbSource.VBProject.VBComponents
       
        bExport = True
        szFileName = cmpComponent.Name

        ''' Concatenate the correct filename for export.
        Select Case cmpComponent.Type
            Case vbext_ct_ClassModule
                szFileName = szFileName & ".cls"
            Case vbext_ct_MSForm
                szFileName = szFileName & ".frm"
            Case vbext_ct_StdModule
                szFileName = szFileName & ".bas"
            Case vbext_ct_Document
                ''' This is a worksheet or workbook object.
                ''' Don't try to export.
                bExport = False
        End Select
       
        If bExport Then
            ''' Export the component to a text file.
            cmpComponent.Export szExportPath & szFileName
           
        ''' remove it from the project if you want
        '''wkbSource.VBProject.VBComponents.Remove cmpComponent
       
        End If
   
    Next cmpComponent

    MsgBox "Export is ready"
End Sub


I get the following error message:
"Run-time error 50035: Method 'Export' of object'_VBComponent' failed"
The following line in VBE is highlighted:
"cmpComponent.Export szExportPath & szFileName"
What am I doing wrong?

Would appreciate any input...


Edited by Sisyphus: Use of code tags corrected.
Code tags come in pairs. When you click the 'Code' button one pair is inserted. Paste your code between them. The opening bracket just says [code]. The closing bracket has the same text with a slash before it.

[SamT]

have you set a reference to
Microsoft Visual Basic for Applications Extensibility 5.3,

AKA Browse to:
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

[Sisyphus]

I don't see the variable FolderWithVBAProjectFiles being declared anywhere.
Have you tested that the folder exists?

[Querious7]

have you set a reference to
Microsoft Visual Basic for Applications Extensibility 5.3,

AKA Browse to:
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

Yes the reference has been set in VBE, TOOLS, REFERENCES to 'Microsoft Visual Basic for Applications Extensibility 5.3'

[Querious7]

I don't see the variable FolderWithVBAProjectFiles being declared anywhere.
Have you tested that the folder exists?

The folder does not exist...the code is supposed to create it if it doesn't exist.

After reading your comment, I tested the macro again after manually creating the referenced file folder in 'My Documents' and got the same error/result.

The problematic line 'cmpComponent.Export szExportPath & szFileName' is supposed to bring together variables that have already been declared 1) szExportPath 2) szFileName

I'm stumped???

Ideas/suggestions much appreciated...

[SamT]

This code:

Code: Select all

 If FolderWithVBAProjectFiles = "Error" Then
        MsgBox "Export Folder not exist"
        Exit Sub
    End If

means that somewhere in your Project Code, there must be a Function named FolderWithVBAProjectFiles.

That is where the error is occurring.

Upload your workbook for us. We don't need to see any sheets of data.

[Querious7]

This code:

Code: Select all

 If FolderWithVBAProjectFiles = "Error" Then
        MsgBox "Export Folder not exist"
        Exit Sub
    End If

means that somewhere in your Project Code, there must be a Function named FolderWithVBAProjectFiles.

That is where the error is occurring.

Upload your workbook for us. We don't need to see any sheets of data.

Thank you sooo much SamT...that's exactly what happened, In going from one workbook to another I missed copying the following function:

Code: Select all

Function FolderWithVBAProjectFiles() As String
    Dim WshShell As Object
    Dim FSO As Object
    Dim SpecialPath As String

Set WshShell = CreateObject("WScript.Shell")
    Set FSO = CreateObject("scripting.filesystemobject")

SpecialPath = WshShell.SpecialFolders("MyDocuments")


If Right(SpecialPath, 1) <> "\" Then
        SpecialPath = SpecialPath & "\"
    End If
   
    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then
        On Error Resume Next
        MkDir SpecialPath & "VBAProjectFiles"
        On Error GoTo 0
    End If
   
    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then
        FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"
    Else
        FolderWithVBAProjectFiles = "Error"
    End If
   
End Function


Now works great...thanks again for pointing me in the right direction