How do I use PDF-eXPLODE with Microsoft Excel?

ISSUE:
Microsoft Excel  worksheets do not have preset pagination like a Word document. The page formatting is generally done during the print stage. Where or how can I insert a PDF-eXPLODE tag so it appears on each page of the Excel report?


ANSWER: 

Use one of the following alternative procedures to explode and send worksheets in an Excel workbook. The first procedure uses Excel's built-in page header feature but requires Excel 2007 if you want the PDF-eXPLODE tag to be hidden by changing the color to white. The second procedure offers a workaround that simulates a page header by repeating the top row of the worksheet on each page. This approach allows you to hide the PDF-eXPLODE tag by applying a white color in any version of Excel. The third method incorporates some VBE (Visual Basic for Excel) code which when run as a macro, is capable of writing the value of a calculated cell into the Left pane of the Custom Header  that is available under Page Setup/Print Title/Custom Header. No current version of Excel (2007/2010/Office 365/2016) is able to calculate and update the Header with any calculated cell value. Hence Procedure C is provided with VBE coding to update one (current) or all worksheets. The instructions are easy to follow and provide the most robust solution. We recommend either Procedure B or Procedure C.

Procedure A:

Using an Excel Page Header

1. On the  File  menu, click  Page Setup , and then click the  Header/Footer  tab.
2. Now click  Custom Header .
3. In the  Left Section , enter the PDF-eXPLODE tag, for example:   <pdfexplode>info@pdf-explode.com</pdfexplode>
4. To add another PDF-eXPLODE tag, press enter to begin the tag on a new line.
5. If you are using Excel 2007, you may select the tag and click the  Format Text  button to apply the color white, thereby making the tag invisible.
6. Click  OK  twice.
7. Repeat for each worksheet in the workbook.
8. On the  File  menu, click  Print .
9. Set the printer to  PDFeXPLODE .
10. Use the  Print what  option to control which worksheets to explode. For example,  to explode and e-mail all worksheets in the workbook, select  Entire workbook , and then click  OK .

Procedure B:

Simulating a Page Header

1. On the first row of a worksheet, enter the PDF-eXPLODE tag, for example:   <pdfexplode>info@pdf-explode.com</pdfexplode>
2. To hide the tag, click the arrow next to the  Font Color  button on the  Formatting  toolbar, and then click the White color sample on the palette.
3. To add another PDF-eXPLODE tag, enter it on the next row.
4. On the  File  menu, click  Page Setup , and then click the  Sheet  tab.
5. To repeat the first row(s) on every page, use the  Print titles  option to select the  Rows to repeat at top . For example, to repeat the first row the entry will be  $1:$1 . Finish by clicking  OK .
6. Repeat for each worksheet in the workbook.
7. On the  File  menu, click  Print .
8. Set the printer to  PDFeXPLODE .
9. Use the  Print what  option to control which worksheets to explode. For example, to explode and e-mail all worksheets in the workbook, select  Entire workbook , and then click  O K

Procedure C:

Updating a Custom Header with VBA Macro code prior to Printing

SET UP VBA CODE:

1. In Excel press Alt + F11 to enter the VBE interface.

2. Press Ctrl + R to show the Project Explorer if the Project window does not display

3.  Click '+' to open the menu Microsoft Excel Objects. This should open and list each worksheet under this Object option plus an additional object named ThisWorkbook

4. Right-click ThisWorkbook to open a Module screen on which you will paste the VBA code for macro execution. 

5. Choose Insert -> Module.

6. Paste code from the yellow background window below, into the right pane.

 7. Your screen is shown below

8. Click the  SAVE  icon on the toolbar and then press Alt + Q to close the VBA interface.

9. Click menu File/Save As  to save the Workbook before any other changes to an Excel-Macro-Enabled Workbook (*.xlsm) so it will preserve the VBA code