Getting an error with excel ole "Add method of Workbooks class failed"

Question

I am trying to open and refresh an excel 2003 spreadsheet via ole. However I am getting the error "Add method of Workbooks class failed" with no further information.

The code works in unit tests, and works on 3 servers but fails with the error on our web server. It is being run from a service app running under the Local System Account.

The same version of excel is installed on all servers (2003 sp3). The file(s) in question all exist and are at the expected location.

There are no macros in the spreadsheets, but there are database queries. The spreadsheets can all be opened.

The calling code is

if VarIsEmpty(XLApp) then
begin
  XLApp := CreateOleObject('Excel.Application');
  try
    XLApp.DisplayAlerts:=  wdAlertsNone;
  except
...
  end;
  XLApp.AutomationSecurity:= msoAutomationSecurityForceDisable;
end;

fullFileName:= ExpandReportFileName( partialFilename);
if not FileExists(fullFileName) then
  raise Exception.Create('File not found: ' + fullFileName);

XLAPP.Workbooks.Add(fullFileName);  << fail here

Any ideas on what else I can try?

Answer 1

Automating Office applications in a service is not supported.

While it is possible, it's very difficult, and you'll run into many problems, such as this one. It'll also be very slow.

You should look for a Delphi component that manipulates Excel files, such as this one. (Note that I'm not specifically recommending that one; it was the first one I found on Google)

Depending on what you're trying to do, you might be able to use OLE DB instead.

Answer 2

Workbooks.Open might be the method you're looking for

Add creates a new empty workbook. If you supply a filename, that file is used as a template for the new file - see here

Open just opens the file as you would expect - see here