CreateOleObject opened workbook won't run macro containing Application.Run "Solver.xlam!..." causes error 400

Question

This problem has a relationship to a previous post I saw (not my question), which was closed:

But I believe I have debugged my issue to a more general OLE/VBA issue (read further below). I can reproduce in Delphi and also in VBS.

My Delphi code is similar to what is presented there - my code is:

uses ComObj, ActiveX; // and a long list of others not relevant

var
    Excel             : OleVariant;
    WkSheet           : OleVariant;
    ...
begin
...

CoInitialize(nil);
// Open Spreadsheet
Excel := CreateOleObject('Excel.Application');
Excel.Workbooks.Open('c:\Local Data\filename.xlsm');
Excel.Visible := true;
Excel.UserControl := True;

WkSheet := Excel.Worksheets.Item['6 Units'];
WkSheet.Select;
// Can write parameters onto sheet in appropriate cells that
// macro expects, but can edit this out with impacting on the error.
// Call the macro
Excel.Run ('sheet2.SolveCCD6');

// This creates a runtime error!

Which for the previous poster generated the error OLE error 800A03EC at Excel.Run.

My VBS script that creates the same error is:

Option Explicit
Dim excelObject

set excelObject = CreateObject("Excel.Application")
excelobject.Workbooks.Open "c:\Local Data\filename.xlsm", 0, true
excelobject.run "sheet2.SolveCCD6"
excelobject.quit
set excelobject = Nothing

(started by double-clicking in Windows Explorer)

I believe the macro is being found as if I rename to a rubbish name ('sheet2.SolveCCD4839') then I get an error about not being able to find the name.

On making the excel object visible with (in Delphi) Excel.Visible=true and Excel.UserControl:=true, I noticed that running the macro from its keyboard shortcut in the Excel window also created an error, but only when the window is opened by CreateOleObject (ie opening the spreadsheet from the Windows GUI creates no issue) - I get VBA error 400.

Here is start of the VBA macro, where the error occurs:

Sub SolveCCD6()

ActiveSheet.Unprotect "name"
Application.Run "Solver.xlam!Solver.Solver2.Auto_Open"


Application.Run "Solver.xlam!SolverReset"
Application.Run "Solver.xlam!SolverOptions", , 2000
Application.Run "Solver.xlam!SolverOK", "$L$70", 2, "0", "$D$8, $D$18, $D$20, $D$30,     $D$32, $D$42, $D$44, $D$54, $D$56, $D$66, $D$68, $D$73"
Application.Run "Solver.xlam!SolverAdd", "K5", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K6", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K17", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K18", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K29", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K30", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K41", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K42", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K53", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K54", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K65", 1, "0.01"
Application.Run "Solver.xlam!SolverAdd", "K66", 1, "0.01"
Application.Run "Solver.xlam!SolverSolve", True

I determined that it is able to begin executing the macro, but it is failing on Application.Run code which tries to use Solver. I found this with msgbox statements highlighting the progress of execution through the macro. For example in the above it stops at:

Application.Run "Solver.xlam!Solver.Solver2.Auto_Open"

And if I comment this out then the same issue appears on all following lines that that try to utilise Solver.xlam, like:

Application.Run "Solver.xlam!SolverReset"

I then set up an error trap to provide more information on the error, which provides:

On Error GoTo Errorcatch
Errorcatch:
MsgBox Err.Description

Method 'Run' of object'_Application' failed

I assume this is some kind of path issue that relates to how CreateOleObject opens the workbook, but am unsure of how to debug this further or correct. When I open this workbook standalone, by double-clicking or from within Excel (opened from Start menu) it runs without any errors.

Additionally, I've noticed that while "Solver" and "Data Analysis" appears on the "Data" menu in Excel what I start from the Windows GUI, they don't appear in the Excel window that is opened by CreateOleObject and Workbooks.Open in Delphi. This adds to my belief that this has something to do with paths or initialisation of the Excel object.

Thanks in advance!

"My code is similar to" is not the same as "Here is my code". The linked question had an answer, and that answer's code works. If you want help with issues with your code, you have to post your code. Linking to similar code isn't meaningful. If you're not sure of the difference, try calling your auto repair shop with: "I won't let you see my car, but it sounds similar to the one that just drove by your shop. What's wrong with it and how much will it cost to fix?" and see if you get an estimate. — Ken White
Hi Ken, Thanks for your reply. I will edit substituting a cut back version of my code. With the linked article, I don't think the respondents considered an interaction between CreateOleObject and the macro, though I realise he also didn't provide his/her VBA code that the Delphi was calling. — Neil Francis
And now I have created a similar problem using VBS (code added above), so I'm more confident that this is an OLE/Excel interaction, and not specifically a Delphi issue. — Neil Francis
Okay - I may have found the solution and am testing this. It is by design that Add-Ins aren't loaded when using Excel in an OLE automation object. The solution appears to be to manually load Solver beforehand. I'm testing adding the line " Excel.Workbooks.Open(Excel.LibraryPath + '\Solver\Solver.xlam')" before trying to run the macro. Ref:support.microsoft.com/kb/213489 — Neil Francis

Neil Francis Neil Francis · Accepted Answer · 2014-02-26T02:21:34

Okay. The problem causing the OLE error 800A03EC, and creating the error 400 in the Excel GUI, when the instance of Excel is created using CreateOleObject (Delphi) or CreateObject (VBS), is that Add-ins are not loaded automatically into instances of Excel created in this way, as they are when starting Excel from GUI. This means that if a macro uses add-ins, like Solver then they need to be loaded manually before using the run method on an Excel object.

The solution is to add the line:

Excel.Workbooks.Open(Excel.LibraryPath + '\Solver\Solver.xlam');

I added it before

Excel.Workbooks.Open('c:\Local Data\filename.xlsm');

In the code above.

This behaviour is described at: http://support.microsoft.com/kb/213489

Not including this step before Excel.Run may have been the issue in the related question:

Excecute Excel vba code through Delphi

CreateOleObject opened workbook won't run macro containing Application.Run "Solver.xlam!..." causes error 400

1 Answers