3

Sorry for the long title.

I have several .xlsm files which share a lot of code, so I moved the repeated parts to an addin .xlam file. I have been using a .vbs script to open all the files one after another and run a macro in each.

Problem

The problem I'm facing is that on the second run of the .vbs script, excel crashes and gives what seems to be a very generic error, said here to be an "Automation Error":

Script: C:\Users\~\Desktop\test\test.vbs
Line: 5
Char: 1
Error: The server threw an exception.
Code: 80010105
Source: (null)

To my surprise, I was able to reproduce this crash even after removing 99% of the content of my files.

test.vbs:

Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\~\Desktop\test\test.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 has a module Module1, test.xlsm has a Module Auto and a Reference to 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

With the function Other() commented out, the code works fine (saying hello, load and goodbye). It also works fine if the macro is run from within excel. Only when Other() is present, and Run() is run through the .vbs file is there an error (right after hello).

Workaround

If I open test.xlsm, save it, and close it again in between each run of test.vbs, there are no problems. I believe this has something to do with the addin, rather than the spreadsheet, because in my original script, which opened multiple excel files, only one file needs to be opened and saved.

I also noticed that the excel file is a little bigger in its "problem" state, and that once I open and save it, it returns to its slightly smaller original size. (EDIT: This is at least partly caused by new cache streams __SRP_4 and __SRP_5 inside the vbaProject.bin file, which I extracted using this answer (oh, and this). After manually deleting all SRP entries, I was able to run the .vbs script again without problems, although just like the open-save-close strategy, it's only temporary, and will then crash on the third run rather than the second.)

Question

Are addins not appropriate for shared code? May they not contain functions? Is there any way to work around this crash besides what I'm doing right now?

Any thoughts are appreciated.

Community
  • 1
  • 1

4 Answers4

0

It sounds to me like the first instance isn't being unloaded/released before the second instance is being called. Perhaps using the Application.Wait Method to wait a few seconds before each subsequent run in performed might help?

'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

To install your add-in to excel via vbscript you can use the following code

'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

If this fails you might have to clear your registry values first, then rerun the above script

'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 Office\Office14\excel.exe"" /unregserver && timeout /t 3 && tskill excel && ""C:\Program Files (x86)\Microsoft Office\Office14\excel.exe"" /regserver",1,True
Set objFSO = Nothing
Set objShell = Nothing
x=msgbox("Excel registry refreshed." ,0, "Registry Update")
wscript.quit
CaffeinatedMike
  • 1,537
  • 2
  • 25
  • 57
  • In my example, I'm just double-clicking on the .vbs file again after it finishes. I can wait for a minute or so in between but the crash still happens the second time. I've also checked that neither `EXCEL.EXE` nor `wscript.exe` were running before clicking the second time. (Although, after the crash, excel is stuck open and has to be killed) –  Apr 01 '16 at 14:21
  • Two things: 1) I believe the issue has to do with the add-in, which could mean that excel isn't recognizing/finding it when it reopens (leading to excel hanging/being unresponsive 2) If you're using the add-in functions and code on many different files over time I would suggest installing the add-in to excel instead of adding a reference to it from vbe (I will update my answer to include how) – CaffeinatedMike Apr 04 '16 at 15:40
  • I had originally saved the addin in excel's special folder under `%AppData%`, where it saves .xlam by default. I think I installed it via the UI before it showed up on the default list of possible references. The reference in the vbe seems to be required to call the sub; I couldn't find another way to do that, even with strange constructions like [this](http://stackoverflow.com/a/2814426/847423) that included the full path of the file in code. I solved the problem by clearing the vba cache and I will post that in a moment. –  Apr 04 '16 at 19:00
0

Unfortunately, I still don't know why this is happening, but I found an automated solution that I'm going to stick with.

As I mentioned in my question, the test.xlsm file was a little bigger in its "problem" state, due at least partially to some kind of cache, of which I could only find one offical mention here:

2.2.6 SRP Streams

Streams that specify an implementation-specific and version-dependent performance cache. MUST be ignored on read. MUST NOT be present on write. The name of each of these streams is specified by the following ABNF grammar:

SRPStreamName = "__SRP_" 1*25DIGIT

My solution was to remove the cache, which I did manually at first with this tool. When that seemed to work, I wrote a Java program to do it automatically (gist here). It's glue between java.util.zip and Apache POIFS.

I also added a line to call the Java at the end of the .vbs script:

CreateObject("WScript.Shell").Run "java -jar clear-excel-cache.jar C:\Users\~\Desktop\test\test.xlsm", 1, false

In my actual .vbs file, which calls multiple excel files in a loop, this line is just inside the loop. There is a little cmd window that opens after each file is run but it no longer crashes on the second run, so I'm calling that a success.

  • There are two ways to get rid of the cmd window I've found: 1) change the 1 to a 7 to start [minimized](https://msdn.microsoft.com/en-us/library/d5fk67ky(v=vs.84).aspx#Anchor_2), or 2) use `javaw` instead of `java` –  Apr 07 '16 at 21:52
0

Your issue could be the same issue which I am trying to resolve - Random 64-bit Excel 2013 VBA crashes (VBE7.dll errors). You can check the Application Event logs for a VBE7.dll crash to confirm this.

In my case various XLSM files become intermittently corrupted through manual use.

My fix as an alternative to yours is the following VBS (anything to trigger a VBA "recompile").

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
hannesRSA
  • 76
  • 1
  • 5
0

FYI - Microsoft released a patch which fixes the issue in Excel 2013 on 3rd May 2016.

https://support.microsoft.com/en-us/kb/3085486

hannesRSA
  • 76
  • 1
  • 5