很抱歉标题太长。
我有几个.xlsm文件,它们共享很多代码,所以我把重复的部分移到了一个addin.xlam文件中。我一直在使用.vbs脚本一个接一个地打开所有文件,并在每个文件中运行一个宏。
问题
我面临的问题是,在.vbs脚本的第二次运行时,excel崩溃,并给出了一个似乎非常常见的错误,这里称为"自动化错误":
Script: C:Users~Desktoptesttest.vbs
Line: 5
Char: 1
Error: The server threw an exception.
Code: 80010105
Source: (null)
令我惊讶的是,即使删除了99%的文件内容,我也能重现这次崩溃。
test.vbs:
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:Users~Desktoptesttest.xlsm")
xlApp.Run "Auto.Run" '<~~ error on this line
xlBook.Save
xlBook.Close (True)
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
test.xlsm:
test.xlam有一个模块Module1,test.xlsm有一个module Auto和一个对test.xlam 的引用
test.xlsm,Auto
:
Sub Run()
MsgBox "hello"
Test.Load
MsgBox "goodbye"
End Sub
test.xlam,Module1
Sub Load()
MsgBox "Load"
End Sub
Function Other()
End Function
注释掉函数Other()
后,代码运行良好(说你好、加载和再见)。如果宏是在excel中运行的,它也可以正常工作。只有当Other()
存在,并且Run()
通过.vbs文件运行时,才会出现错误(就在hello之后)。
解决方案
如果我打开test.xlsm
,保存它,并在每次运行test.vbs
之间再次关闭它,就没有问题。我相信这与插件有关,而不是与电子表格有关,因为在我打开多个excel文件的原始脚本中,只需要打开和保存一个文件。
我还注意到,excel文件在"问题"状态下有点大,一旦我打开并保存它,它就会恢复到稍小的原始大小。(编辑:这至少部分是由vbaProject.bin
文件中的新缓存流__SRP_4和__SRP_5引起的,我使用这个答案提取了这些缓存流(哦,还有这个)。在手动删除所有SRP条目后,我能够毫无问题地再次运行.vbs脚本,尽管就像打开-保存-关闭策略一样,它只是暂时的,然后会在第三次运行时崩溃,而不是在第二次运行时。)
问题
加载项是否不适合共享代码?它们可能不包含函数吗?除了我现在正在做的事情之外,还有什么办法来解决这次撞车事故吗?
任何想法都值得赞赏。
在我看来,第一个实例在调用第二个实例之前没有被卸载/释放。也许使用Application.Wait方法等待几秒钟,然后再执行后续的每次磨合可能会有所帮助?
'Open file1
'Run macro from file1
'Close file1
Application.Wait(Now + TimeValue("0:00:10")) 'wait 10 seconds
'Open file1
'Run macro from file1
...
...
So on
要通过vbscript将您的加载项安装到excel,您可以使用以下代码
'Launch Excel
set objExcel = createobject("Excel.Application")
strAddIn = "ESP Assistant.xlam"
'~~> Path where the XLAM resides
SourcePath = "Your source path" & strAddIn
'Add the AddIn
On Error Resume Next
With objExcel
'Add Workbook
.Workbooks.Add
'Show Excel
objExcel.Visible = True
.AddIns.Add(SourcePath, False).Installed = True
End With
如果失败,您可能必须首先清除注册表值,然后重新运行上面的脚本
'File to use just in case Add-In installation fails
'Refreshes Excel Registry Entries to allow for clean install of Add-In
Dim objFSO, objShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = WScript.CreateObject ("WScript.shell")
objShell.Run "cmd /c ""C:Program Files (x86)Microsoft OfficeOffice14excel.exe"" /unregserver && timeout /t 3 && tskill excel && ""C:Program Files (x86)Microsoft OfficeOffice14excel.exe"" /regserver",1,True
Set objFSO = Nothing
Set objShell = Nothing
x=msgbox("Excel registry refreshed." ,0, "Registry Update")
wscript.quit
不幸的是,我仍然不知道为什么会发生这种情况,但我找到了一个我将坚持使用的自动化解决方案。
正如我在问题中提到的,test.xlsm文件在"问题"状态下有点大,至少部分原因是某种缓存,我在这里只能找到一个官方提到的缓存:
2.2.6 SRP流
指定特定于实现且依赖于版本的性能缓存的流。必须是读取时忽略。书写时不得在场。每个流的名称由以下ABNF语法指定:
SRPStreamName = "__SRP_" 1*25DIGIT
我的解决方案是删除缓存,起初我用这个工具手动删除了缓存。当这似乎奏效时,我编写了一个Java程序来自动完成(这里的要点)。它是java.util.zip和ApachePOIFS之间的粘合剂。
我还在.vbs脚本的末尾添加了一行调用Java的代码:
CreateObject("WScript.Shell").Run "java -jar clear-excel-cache.jar C:Users~Desktoptesttest.xlsm", 1, false
在我实际的.vbs文件中,它在一个循环中调用多个excel文件,这一行就在循环中。每个文件运行后都会打开一个小cmd窗口,但它在第二次运行时不再崩溃,所以我称之为成功。
您的问题可能与我试图解决的问题相同-随机64位Excel 2013 VBA崩溃(VBE7.dll错误)。您可以检查应用程序事件日志中的VBE7.dll崩溃以确认这一点。
在我的情况下,各种XLSM文件会因手动使用而间歇性损坏。
我的修复程序是以下VBS(任何触发VBA"重新编译"的程序)。
Resave "myfile.xlsm"
Sub Resave(filename)
Set objExcel = CreateObject("Excel.Application")
currentDirectory = left(WScript.ScriptFullName,(Len(WScript.ScriptFullName))-(len(WScript.ScriptName)))
objExcel.Application.AutomationSecurity = 3 ' Disable to avoid crash
objExcel.Application.enableevents = False
objExcel.Application.Workbooks.open(currentDirectory + "" + filename)
objExcel.Application.Visible = True
objExcel.Application.DisplayAlerts = False
Set objSheet = objExcel.ActiveWorkbook.Sheets.Add
objSheet.Delete
objExcel.Application.DisplayAlerts = True
objExcel.Application.enableevents = True
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
Set objExcel = Nothing
End Sub
FYI-微软于2016年5月3日发布了一个补丁,修复了Excel 2013中的问题。
https://support.microsoft.com/en-us/kb/3085486