How to reference VBIDE.VBComponent from a VSTO Addin?

I'm looking at creating a simple VSTO .net addin for Microsoft Excel 2003/2007/2010 that would allow me to automatically export all the code in a workbook to files.http://www.cpearson.com/excel/vbe.aspx highlights a way to do  this in VBA, but I would like to do this for lots of different workbooks, hence the addin approach.

If I try and copy the code from "Exporting A VBComponent Code Module To A Text File" in the link above to a new VS 2008 Excel Addin I get VBIDE reference compile issues.  I'm strugling to work out how to reference the Code files from an addin, so I can export the code quickly. Can anyone point me in the correct direction.

Public Function ExportVBComponent(VBComp As VBIDE.VBComponent, _
                FolderName As String, _
                Optional FileName As String, _
                Optional OverwriteExisting As Boolean = True) As Boolean
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' This function exports the code module of a VBComponent to a text
        ' file. If FileName is missing, the code will be exported to
        ' a file with the same name as the VBComponent followed by the
        ' appropriate extension.
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim Extension As String
        Dim FName As String
        Extension = GetFileExtension(VBComp:=VBComp)
        If Trim(FileName) = vbNullString Then

            FName = VBComp.Name & Extension
        Else
            FName = FileName
            If InStr(1, FName, ".", vbBinaryCompare) = 0 Then
                FName = FName & Extension
            End If
        End If

        If StrComp(Right(FolderName, 1), "\", vbBinaryCompare) = 0 Then
            FName = FolderName & FName
        Else
            FName = FolderName & "\" & FName
        End If

        If Dir(FName, vbNormal + vbHidden + vbSystem) <> vbNullString Then
            If OverwriteExisting = True Then
                Kill(FName)
            Else
                ExportVBComponent = False
                Exit Function
            End If
        End If

        VBComp.Export(FileName:=FName)
        ExportVBComponent = True

    End Function

    Public Function GetFileExtension(ByVal VBComp As VBIDE.VBComponent) As String
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' This returns the appropriate file extension based on the Type of
        ' the VBComponent.
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Select Case VBComp.Type
            Case vbext_ct_ClassModule
                GetFileExtension = ".cls"
            Case vbext_ct_Document
                GetFileExtension = ".cls"
            Case vbext_ct_MSForm
                GetFileExtension = ".frm"
            Case vbext_ct_StdModule
                GetFileExtension = ".bas"
            Case Else
                GetFileExtension = ".bas"
        End Select

    End Function

 


Jamie Clayton http://www.jenasysdesign.com.au
Answer
Answer

What exactly do you mean by "I get VBIDE reference compile issues"? What specific problems are you having? I would just create an XL2007 add-in. I assume you have a reference to "Microsoft.Vbe.Interop" and an Import statement for that library (Imports Mircosoft.Vbe.Interop).  To avoid name collisions with other libraries, I generally use "Imports VBA = Mircosoft.Vbe.Interop" and then prefix the VBE objects with "VBA". If you do this, you'll need to change any variables declared as VBIDE.ObjectName to VBA.ObjectName. E.g., change As VBIDE.VBComponent to As VBA.VBComponent.

 

 

 


Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com
Cordially,
Chip Pearson
Excel MVP 1998 - 2014
Pearson Software Consulting, LLC
www.cpearson.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated October 5, 2021 Views 2,119 Applies to: