是否有战术(读取’黑客’)解决方案以避免将VBA代码和Excel表作为一个二进制文件?

Question

是否有战术(读取’黑客’)解决方案以避免将VBA代码和Excel表作为一个二进制文件?

Is there a tactical (read 'hack') solution to avoid having the VBA code and Excel sheet as one binary file?

来源: link 标签:excel vba excel-vba 更新时间: 2020-12-01

最佳答案

我为Excel编写了一种构建系统,它从源文件导入VBA代码(然后可以导入到源控件,扩展等).它可以通过创建包含导入代码的新Excel文件来工作,因此它可能在你的情况下不起作用.

构建宏看起来像这样,我将其保存在一个名为Build.xls的文件中:

 Sub Build()
    Dim path As String
    path = "excelfiles"

    Dim vbaProject As VBIDE.VBProject
    Set vbaProject = ThisWorkbook.VBProject

    ChDir "C:\Excel"
    ' Below are the files that are imported
    vbaProject.VBComponents.Import (path & "\something.frm")
    vbaProject.VBComponents.Import (path & "\somethingelse.frm")

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "Output.xls"
    Application.DisplayAlerts = True

    Application.Quit
End Sub

现在,VBIDE的东西意味着您必须导入一个名为“Microsoft Visual Basic for Applications Extensability 5.3”的引用,我认为。

当然,必须启动Excel来构建仍然存在问题.这可以用一个小的VB脚本修复:

 currentPath = CreateObject("Scripting.FileSystemObject") _
    .GetAbsolutePathName(".")
filePath = currentPath & "\" & "Build.xls"

Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
    .Workbooks.Open(filePath)
    .Application.Run "Build.Build"
End With
Set objXL = Nothing

运行上述脚本应该启动输出结果表的构建Excel文件.您需要更改一些东西才能在文件系统中移动.希望这有助于!

Jonas