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