jkluio668
11/1/2017 - 2:25 AM

func_export and import

'需先引用“Microsoft Visual Basic for Applications Extensibility 5.3”,和“信任对vba工程的访问”
Public Sub exportAndImport()
    Dim wkBook As Excel.Workbook
    Dim wkComp As VBIDE.VBComponent
    Dim macroPath As String
    Set wkBook = ThisWorkbook

    On Error Resume Next
    macroPath = ThisWorkbook.Path & "\" & "export\"
    '需添加,如果不存在,就创建文件夹
    For Each wkComp In wkBook.VBProject.VBComponents 'export
        If wkComp.Type = vbext_ct_StdModule Then '如果是模块就导出并删除
            wkComp.Export macroPath & wkComp.Name & ".bas"
            'wkBook.VBProject.VBComponents.Remove wkComp
        End If
    Next
End Sub

Public Sub improt()
    Dim wkBook As Excel.Workbook
    Dim wkComp As VBIDE.VBComponent
    Dim macroPath As String
    Set wkBook = ThisWorkbook
    
    macroPath = ThisWorkbook.Path & "\" & "export\"
    '需添加,如果文件夹不存在,就报错并停止
    tempfile = Dir(macroPath & "*.bas")
    While tempfile <> ""
        Set wkComp = wkBook.VBProject.VBComponents.Import(macroPath & tempfile) '导入代码
        wkComp.Name = Left(tempfile, Len(tempfile) - 4)
        tempfile = Dir
    Wend
    Debug.Print "in export and import"
End Sub


''(运行前:
''1、引用Microsoft Visual Basic for Application Extensibility 5.3,
''2、并且选择信任对VBA工程访问)
Sub 批量导出VBE模块()
    Dim ExportPath As String, ExtendName As String
    Dim vbc As VBComponent
    Dim i%
    ExportPath = ThisWorkbook.Path
    For Each vbc In Application.VBE.ActiveVBProject.VBComponents
        '取得模块内代码行数
        i = ThisWorkbook.VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines
        
        ''判断模块是否为空,也就是代码行数是否>=1。
        If i >= 1 Then
            Select Case vbc.Type
            Case vbext_ct_ClassModule, vbext_ct_Document    '组件属性为类模块、EXCEL对象
                ExtendName = ".Cls"    '设置导出文件的扩展名
            Case vbext_ct_MSForm    '组件属性为窗体
                ExtendName = ".frm"
            Case vbext_ct_StdModule    '组件属性为模块时
                ExtendName = ".Bas"
            End Select
            If ExtendName <> "" Then
                vbc.Export ExportPath & "\" & vbc.Name & ExtendName
            End If
        End If
    Next

End Sub