exceldelphiole

Name conflict in Excel files by OLE


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

Screenshot

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.


Solution

  • 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);