Excel PageSetup multiple sheets

Question

I have a Delphi routine which runs through a list of Excel files, opens them with MS Excel, iterates through the sheets setting the PageSetup for each sheet so all columns will fit on one page and then export all sheets to a single PDF.

MSApp := CreateOLEObject('Excel.Application');
doc := MSApp.WorkBooks.Open(fileOpenDialog1.FileName, False, True, EmptyParam, '', '',
  True, EmptyParam,  EmptyParam, EmptyParam, False, EmptyParam, False, False, EmptyParam);

MSApp.PrintCommunication := False;
doc.Sheets.Item[1].PageSetup.FitToPagesWide := 1;
if doc.Sheets.Count > 1 then
begin
  doc.Sheets.Item[1].Select(True);
  for i := 2 to doc.Sheets.Count do
  begin
    doc.Sheets.Item[i].Select(False);
    doc.Sheets.Item[i].PageSetup.FitToPagesWide := 1;
  end;
end;

doc.ExportAsFixedFormat(xlTypePDF, outPutFile, EmptyParam, False,
  EmptyParam, EmptyParam, EmptyParam, False, EmptyParam);

MSApp.PrintCommunication := True;

If there is only one sheet setting FitToPagesWide to 1 page works as expected. However, if there is more than one page and say the first page is too wide, the changes to the PageSetup for the first page do not stick and are lost by the time the file is exported.

Looking at the various examples online and the MS docs, I have been unable to find anything to suggest that the above should not work as expected or that there is some property that needs to be set in order to make PageSetup changes stick before moving on to the next sheet.

I could save each sheet to PDF individually and then merge the PDFs, but it seems to me that shouldn't be necessary.

When you do the operation manually with Excel, does the "fit to page" setting works as you expect? — fpiette
I'm a bit confused. When your code doesn't work as expected? When you have one sheet that cant fit to single page or when you have multiple sheets in one file? — SilverWarior
I had this issue, even posted a question about it. Try setting the PageSetup.Zoom := false before setting FitToPagesWide. — John Easley
@fpiette - you can't do the operation manually AFAIK (although I am no Excel guru). In Excel you have to go to the backstage choose print and then select the "Fit All Columns on One Page". From print you can also choose to print the entire workbook with these settings. However, if you choose save as and select pdf, I think you can only save the current sheet not the workbook. — Mark Williams
@SilverWarior - If there is a single sheet it works fine. If there are multiple sheets and one of those sheets has columns that will extend on to a new page that is where I am having the problem. The setting of properties for pageSetup as you iterate through each sheet seems to be lost when you move on to the next sheet. — Mark Williams

Peter Wolf Peter Wolf · Accepted Answer · 2020-11-11T07:55:38

The issue you observe is caused improper use of Application.PrintCommunication property. All changes to PageSetup are cached from the moment when you set it to False until those changes are commited by setting the property value back to True. But you do that after exporting the workbook via ExportAsFixedFormat. The solution is simple:

either don't use PrintCommunication
or set its value to True before the export

Now why the first worksheet is exported in the layout as you expected? I can only guess that it's because it was already saved with width shrinking configured on the first worksheet only:

Here are some further observations:

There's no need to select/deselect a worksheet before/after changing its PageSetup.
There's no need to handle the first worksheet as a special case. You can set them all up in a single loop.
I would preemptively set FitToPagesTall to 0 (or False) in case the worksheet has configured height shrinking.

Of course the recommended way to perform multiple changes to PageSetup is within PrintCommunication := False block. With the above applied you get:

procedure ExportWorkbookAsPDF(const InputFileName, OutputFileName: string);
var
  App, Workbook, PageSetup: OleVariant;
  Index: Integer;
begin
  App := CreateOleObject('Excel.Application');
  try
    App.DisplayAlerts := False;
    App.Interactive := False;
    Workbook := App.Workbooks.Open(FileName := InputFileName, UpdateLinks := False,
      ReadOnly := True, IgnoreReadOnlyRecommended := True);
    try
      App.PrintCommunication := False;
      for Index := 1 to Workbook.Sheets.Count do
      begin
        PageSetup := Workbook.Sheets.Item[Index].PageSetup;
        PageSetup.FitToPagesWide := 1;
        PageSetup.FitToPagesTall := 0;
      end;
      App.PrintCommunication := True;
      Workbook.ExportAsFixedFormat(xlTypePDF, OutputFileName);
    finally
      Workbook.Close(False);
    end;
  finally
    App.Quit;
  end;
end;

In your original code you use Workbook.Sheets collection to iterate over all sheets in a workbook, which includes also charts. To apply PageSetup to worksheets only use Workbook.Worksheets collection. See Worksheets vs Sheets for more information.

Excel PageSetup multiple sheets

1 Answers