Name conflict in Excel files by OLE

Question

Developing program for converting from Excel files to PDF. Here is my code:

  procedure ExcelToPDF(const InputFileName, OutputFileName: string);
  var
    Excel: Variant;
    Workbook: Variant;
  begin
    try
      CoInitializeEx(nil, 0);

      //Открытие Excel
      Excel := CreateOleObject('Excel.Application');
//      Excel.Application.EnableEvents := False;
      Excel.Application.DisplayAlerts := False;
      Excel.Visible := False;

      if not VarIsNull(Excel) then
      begin
        //Открытие файла
        Workbook := Excel.Workbooks.Open(InputFileName);
        //Сохранение в PDF
        Workbook.ExportAsFixedFormat(xlTypePDF, OutputFileName);        

        Writeln('OK');
      end
      else
      begin
        raise Exception.Create('Не удалось открыть Excel');
      end;
    finally
      if not VarIsNull(Excel) then
      begin
        Excel.Quit;
        Excel := 0;
      end;
    end;
  end;

It works fine, but for some workbooks there is an error input dialog, when program trying open workbook (also look at screenshot):

Name conflict
Name cannot be the same as a built-in name.
Old name: Print_Area
New name:
OK Cancel

Old name can be Print_Area or _FilterDatabase or something else. It's very common error but I didn't find real solution. But the key question is here: when I open problem workbook in Excel by myself, it works OK without any dialogs, it appears only when I open it in my program by OLE. How can it be if in OLE we just using Excel? So maybe Excel open files with other parameters? I was experimenting with Open method parameters but nothing.

Did you found what is particular inside the workbook producing the error? Can you make available such a workbook for download? Finally, could you use the debugger to see which of your source code line triggers the error? — fpiette
Problem in line Workbook := Excel.Workbooks.Open(InputFileName); I found in Internet some random file with same problem and testing with it. You can download it by link: easyupload.io/wary4j — m1know
Excel 2019 don't want to load your file. The error message is (More or less: translated from French): "Sorry... We found a problem into the content of testbug.xlsb, but we can try to get back as much as possible content. If you are confident with the source, click yes". I have canceled... — fpiette
Based on error I believe you have multiple objects inside your excel file with the same name. By default this is not possible since Excel as a program prevents you from giving same name to multiple objects. But if the said excel file was built through some automation it is possible that rule for not giving same name to multiple objects has not been followed. — SilverWarior
The names you observe are Reserved Names in Excel. They are created automatically by activating particular functions in Excel. I don't know why they cause conflicts, though. There is a similar (unresolved) question that indicates you can open such workbooks via Office interop, but not via OLE automation. — Peter Wolf

m1know m1know · Accepted Answer · 2020-10-07T07:12:05

Thank you for all your comments. I found solution. In VBA there is GetObject function. I found in internet Delphi implemention for it. There it is:

  function GetObject(const AFileName: TFileName): IDispatch;
  var
    vDispatch : IDispatch;
    vBindCtx : IBindCtx;
    vMoniker : IMoniker;
    vChEaten : Integer;
  begin
    Result := nil;
    vDispatch := nil;
    vBindCtx := nil;
    if CreateBindCtx(0, vBindCtx) = S_OK then
    begin
      vMoniker := nil;
      if MkParseDisplayName(vBindCtx, PWideChar(WideString(AFileName)),
        vChEaten, vMoniker) = S_OK then
      begin
        if vMoniker.BindToObject(vBindCtx, nil, IDispatch, vDispatch) = S_OK then
          Result := vDispatch;
      end;
    end;
  end;

So, instead of these two lines:

Excel := CreateOleObject('Excel.Application');
Workbook := Excel.Workbooks.Open(InputFileName);

Use only one:

Workbook := GetObject(InputFileName);

Name conflict in Excel files by OLE

1 Answers