In a Delphi application I am using since years the following code to export xlxs to pdf:

function TExportTool.ExportExcelToPDF(aFileName, aNewFileName: String): Boolean;
// reference : http://embarcadero.newsgroups.archived.at/public.delphi.oleautomation/200811/081103142.html
// unluckily the link above is dead
{- Sheet is counted from 1 and upwards !! }
Var
  App,oWB,oSheet : OleVariant;
begin
  Result := False;
  App:= CreateOleObject('Excel.Application');
  Try
    App.Visible:= 0;
    oWb := App.WorkBooks.Open(ExpandUNCFileName(afilename),1);  // Open read only
    Try
     oSheet := oWB.ActiveSheet;
     oSheet.ExportAsFixedFormat(0,  //xlTypePDF is constant 0
                            aNewFileName,
                            EmptyParam, 
                            EmptyParam, 
                            EmptyParam, // this should be IgnorePrintAreas
                            EmptyParam,
                            EmptyParam,
                            EmptyParam,
                            EmptyParam
                            );
    Finally
    End;
    Result := True;
  Finally
    App.Quit;
    App:= UnAssigned;
  End;
end;

// IMPROVED WORKING CODE FOLLOWS

function TExportTool.ExportExcelToPDF(aFileName, aNewFileName: String): Boolean;
// reference : http://embarcadero.newsgroups.archived.at/public.delphi.oleautomation/200811/081103142.html
{- Sheet is counted from 1 and upwards !! }

procedure RestoreOriginalPrintArea (oSheet: OleVariant);
// Excel loses print area settings in non-English version of application when file is opened using automation:
// https://stackoverflow.com/questions/71379893/exportasfixedformats-ignoreprintareas-parameter-seems-not-to-have-effect
var
  i:Integer;
begin
  for  i:= 1 to oSheet.Names.Count do
  begin
   if VarToStr(oSheet.Names.Item(i).Name).EndsWith('!Print_Area') then
   begin
     oSheet.PageSetup.PrintArea:='Print_area';
     Break;
   end;
  end;
end;

Var
  App,oWB,oSheet : OleVariant;
  i:Integer;
begin
  Result := False;
  App:= CreateOleObject('Excel.Application');
  Try
    App.Visible:= 0;
    oWb := App.WorkBooks.Open(ExpandUNCFileName(afilename),1);  // Open read only
    Try
     oSheet := oWB.ActiveSheet;
     RestoreOriginalPrintArea(oSheet);  // workaround
     oSheet.ExportAsFixedFormat(0,  //xlTypePDF is constant 0
                            aNewFileName,
                            0, // standard quality = 0, Max quality = 1
                            false, //include doc properties
                            false, //ignore print area
                            EmptyParam,
                            EmptyParam,
                            EmptyParam,
                            EmptyParam
                            );
    Finally
    End;
    Result := True;
  Finally
    oWB.Close(false); // better to close the WorkBook too
    App.Quit;
    App:= UnAssigned;
  End;
end;

Now i realized that the pdf created with this code behave like when saving to pdf from Excel using the option "Ignore Print areas" (it is one of the options of the export to pdf from Excel feature).

So I decided to "uncheck" that checkbox also from code and I studied the parameters of ExportAsFixedFormat (reference here).

The fifth parameter is IgnorePrintAreas, so I was assuming that passing False to it, the print areas would have been ignored.

I tried several common sense solution, including:

• passing only that parameter (passing either True or False )
• passing all the first 5 parameters (just in case they are mandatory at runtime)

but no result: the pdf created by my application still "ignores the print areas".

Does anyone has a suggestion or has experience on this specific subject to give me a pointer to fix this issue?

Thanks.

UPDATE

Thanks to the useful accepted answer I appended to the code above the solution for reference, notice two things:

1. the RestoreOriginalPrintArea procedure that contains the workaround
2. the call to oWB.Close(false) at the end

CodePudding user response：

Root cause of error:

Excel loses print area settings in non-English version of application when file is opened using automation.

Why this is happening:

When you define print area in a sheet, Excel internally creates a named range. It has two properties defining its name:

1. Name this property is always of the form WorksheetsName!Print_Area (if the sheet's name contains some special characters it is also enclosed in single quotes).
2. NameLocal has similar structure, but the second part is translated into the language of the application.

This is what it looks like when you open the file in Excel and inspect these properties in VBA, but when you open the same file using automation (for example using the code in question), then NameLocal is no longer translated. This bug causes the named range to not be recognized correctly as print area. oSheet.PageSetup.PrintArea returns an empty string.

Workaround:

Restore original print area after opening the file using:

oSheet.PageSetup.PrintArea:='Print_Area';

This line of code will throw an exception when there was no print area defined in sheet, so there are two options:

1. Place the line inside try..except block.
2. Iterate the Names collection and look for a Name ending with !Print_Area, for example:

var i:Integer;
for  i:= 1 to oSheet.Names.Count do
begin
  if  VarToStr(oSheet.Names.Item(i).Name).EndsWith('!Print_Area') then
  begin
    oSheet.PageSetup.PrintArea:='Print_area';
    Break;
  end;
end;

Other important change:

Because the file could have been modified you also need to add:

oWB.Close(false); //do not save changes

before closing the application, otherwise each call to this function would result in another Excel process still running invisible.