CreateOleObject打开的工作簿将不会运行包含Application的宏.运行"Solver.xlam！..."导致错误400 [英] CreateOleObject opened workbook won't run macro containing Application.Run "Solver.xlam!..." causes error 400
问题描述
此问题与我之前看到的上一篇帖子(不是我的问题)有关，该帖子已经关闭:
This problem has a relationship to a previous post I saw (not my question), which was closed:
但是我相信我已经将我的问题调试为更常见的OLE/VBA问题(在下面进一步阅读).我可以在Delphi和VBS中进行复制.
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.
我的Delphi代码类似于此处显示的代码-我的代码是:
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!
对于先前的海报，在Excel.Run上生成了OLE错误800A03EC.
Which for the previous poster generated the error OLE error 800A03EC at Excel.Run.
我创建相同错误的VBS脚本是:
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
(通过在Windows资源管理器中双击开始)
(started by double-clicking in Windows Explorer)
我认为找到该宏的方式就像是将其重命名为垃圾名称("sheet2.SolveCCD4839")一样，然后我收到一个无法找到该名称的错误消息.
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.
在使用Excel.Visible = true和Excel.UserControl:= true(在Delphi中)使excel对象可见时，我注意到在Excel窗口中通过其键盘快捷键运行宏也会产生错误，但仅当CreateOleObject打开窗口(即从Windows GUI打开电子表格不会产生任何问题)-我收到VBA错误400.
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.
这是发生错误的VBA宏的开始:
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
我确定它可以开始执行宏，但是在尝试使用Solver的Application.Run代码上失败.我发现这是通过msgbox语句突出显示了通过宏执行的进度.例如，在上面，它停在:
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"
如果我将其注释掉，则以下所有尝试利用Solver.xlam的行上都会出现相同的问题，例如:
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
对象'_Application'的方法'运行'失败
Method 'Run' of object'_Application' failed
我认为这是与CreateOleObject如何打开工作簿有关的某种路径问题，但是不确定如何进一步调试或更正.当我独立打开此工作簿时，通过双击或在Excel中(从开始"菜单打开)打开它，不会出现任何错误.
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.
此外，我注意到，虽然从Windows GUI开始，"Solver"和数据分析"出现在Excel的数据"菜单上，但它们没有出现在由CreateOleObject打开的Excel窗口中和工作簿.在Delphi中打开.这使我相信，这与Excel对象的路径或初始化有关.
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.
提前谢谢！
推荐答案
好的.使用CreateOleObject(Delphi)或CreateObject(VBS)创建Excel实例时，导致OLE错误800A03EC和在Excel GUI中创建错误400的问题是，加载项没有自动加载到创建的Excel实例中这样，就像从GUI启动Excel时一样.这意味着，如果宏使用诸如Solver之类的加载项，则需要在Excel对象上使用run方法之前手动加载它们.
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');
我之前添加过
Excel.Workbooks.Open('c:\Local Data\filename.xlsm');
在上面的代码中.
在以下情况中描述了此行为: http://support.microsoft.com/kb/213489
This behaviour is described at: http://support.microsoft.com/kb/213489
在Excel.Run之前不包括此步骤.
Not including this step before Excel.Run may have been the issue in the related question:
这篇关于CreateOleObject打开的工作簿将不会运行包含Application的宏.运行"Solver.xlam！..."导致错误400的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
