3000AD Knowledge Base
Home » Categories » Multiple Categories

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 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

 

EXAMPLE OF PDF-EXPLODE TAG:

Let's assume that you have a Workbook with multiple worksheets, each with data populated across every sheet in the same cell format. So assume cell  D10 is the invoice number in each worksheet, B10 is the Client ID and B11 is the Client Name, B12,13,14 have the Address1, City/State/Zip code and B15 is the company e-mail address.

Let us assume you have configured  PDF-eXPLODE Options Variables screen: Filename: <Var1>, Sub-Folder name: <Var2> Use Variable in e-mail : checked, Message ID : Invoices has been configured

    

In your Excel workbook,

 

cell A1000 ( this is the one in the VBA code that we refer to. You can change to another cell outside the print area and make sure you change the code too ) will contain a formula of:

= "<pdfexplode>" & [Invoice-No] & "," & [Company-name] & "," & [Company-Acc-ID] & "%Invoices#" & B15 & "</pdfexplode>"

 

In Excel, this is how cell A1000 looks:

 

TEST THE CODE:

1. Enter some calculated data in cell A1000 (on each sheet)
2. Press Alt + F8 to display the Macro dialog box.
3. Choose the macro AddHeaderToAll_FromEachSheet and click Run.
4. Once it has run, go to Page Setup|Print Titles and select Header/Footer tab and verify by clicking the  Custom Header ,that your header has been set correctly.
5. To note, the code is set to show the color of the font as vbBlack. Change this to vbWhite to make the font white


NOTE:
Always run the Macro Before Printing to PDFeXPLODE Printer
 
 
SOURCE CODE:   
Copy the code below from "Option Explicit" to "End Function" and paste it into your Module window. Make changes based on the notes/comments (in green)  provided below
AddHeaderToAll_FromEachSheet

Option Explicit

Sub AddHeaderToAll_FromEachSheet()

'  Add a calculated cell (A1000) on each sheet in this workbook.  When you run the MACRO "AddHeaderToAll_FromEachSheet",
'  the code below will loop through each worksheet and update the Custom Header - Left Pane on that worksheet with the value
'  of A1000 for that worksheet

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    '  for Header and footer font color - use: vbWhite - WHITE, vbBlack - BLACK, vbRed - RED, vbBlue - BLUE
    '  7 = font size .  Change .LeftHeader to .LeftFooter to change the position of the PDF-eXPLODE Tag
    ws.PageSetup.LeftHeader = HdrFtr(ws.Range("A1000").Value, "Arial,Regular", vbBlack, 7)
Next ws

End Sub

 

Function HdrFtr(sText As String, Optional ByVal sFont As String, Optional iColor As Long, Optional iFontSize As Long) As String

Dim sColor As String
Dim sFontSize As String

If Len(sFont) Then sFont = "&""" & sFont & """"

If Abs(iFontSize) Then sFontSize = "&" & Abs(iFontSize)

If iColor <> 0 Then
   sColor = "&K" & _
   Right("0" & Hex(iColor And &HFF), 2) & _
   Right("0" & Hex(iColor \ &H100 And &HFF), 2) & _
   Right("0" & Hex(iColor \ &H10000 And &HFF), 2)
End If

HdrFtr = sFont & sFontSize & sColor & sText

End Function  

Comments Comments
There are no comments for this article. Be the first to post a comment.
Name
Email
Security Code Security Code

Subscribe to Knowledge Base

Get notified when new articles are added to the knowledge base.