CODEWITHSUNDEEP
exceldelphi
Mark Williams
Mark Williams

Reputation: 334

Excel PageSetup multiple sheets

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.

Upvotes: 2

Views: 1133

Answers (1)

Peter Wolf
Peter Wolf

Reputation: 3830

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: Page layout

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.

Upvotes: 2

Related Questions