How To Use The E2P Add-in

The main functionality of the E2P add-in is to automate the manual copy/pasting you routinely do between your Excel data and your PowerPoint presentations.  E2P works with copying any Tables, NamedRanges, Pictures, or Shapes inside your workbook.  Simply setup your E2P worksheet inside your desired workbook and let the add-in work for you.  Read on to learn all the features of this add-in.

If you need help with installing your E2P add-in file, please click here.

To enter in your License Code, you will need to click the Dialog Launcher button (shown below) to bring up the Settings userform. From there you can click the Certificate icon in the upper right-hand corner and you will be prompted to enter the license code you were emailed with your file download.

Instructional Videos…

Overview

VBA integration tips & examples are also included in Section H.

1. Inserting An E2P Sheet

In order to use the E2P add-in with your workbook, you first need to insert the E2P mapping sheet.  This worksheet (entitled E2P) will allow you to map out exactly what you want copied from Excel and where you want it placed inside your PowerPoint presentation.  To insert the tab, simply click the first icon in the E2P ribbon tab and you will see a new worksheet added to your Excel workbook in the first tab position.

You may rename or re-position the worksheet if you would like.

The E2P table is where all the action happens. This will act as your map for how you want your PowerPoint presentation populated/created.

1. Adding A Row

Select a cell or multiple cells within the E2P table and click on the Add Row button to add a table row(s) below the selected cell.

2. Deleting A Row

Select a cell or multiple cells within the E2P table and click on the Delete Row button to delete the selected cell row(s) from the table. Be careful you cannot undo this!

3. Refresh Drop Downs

If you make modifications to your spreadsheet that involve objects (anything E2P can export) you may want to refresh the drop down menu in the Objects to Export column.  Modifications can include deleting, adding, or changing the name of an object.  Click the Refresh Drop Downs button to get the most up-to-date object names in your drop down lists.

1. Object To Export

This column is where you need to tell E2P which object(s) you want copied into your PowerPoint presentation.  There are three types of categories you can copy from Excel using the E2P add-in:

• Tables
• Named Ranges
• Shapes/Images/Charts
• Chart Sheet Objects ARE NOT currently supported!

Each object found inside your Excel workbook will show up in a drop down menu in each cell residing in the Object To Export column of the E2P table.  There are specific formats and indicators for each type of object.

Tables – ListObject tables will have the following format: [worksheet name] [^^] [table name]

Shapes/Images/Charts – Any image, shape, or graphical chart inserted into a worksheet will have the following format: [worksheet name] [::] [object name]

Name Ranges – Named Ranges do not have any indicators associated with them.  Only there name will display.  There will be a difference in the name displayed based on the scope of the named range.  

• Global Scope: Range1
• Worksheet Scope: Sheet1!Range1

2. Object Name

This is the name that you wish to call the newly pasted PowerPoint object.  It is important to keep a consistent and unique name, as E2P will go ahead and delete the previous named object (if it exists).  This essentially gives you the ability to replace your pasted images when you update your PowerPoint presentations.

3. Slide #

Indicate which slide you want to paste the object to.

4. Position Your Object (Left, Top, Height, Width)

Specify the exact position you want the object moved or resized to.  If you do not know what values to input, you can use the Dimension Finder tool in the E2P ribbon tab to find this data from inside a PowerPoint presentation.  See the Dimension Finder section below for more details.

If you do not wish to adjust any of these properties, you can leave them blank (starting with version 2.5 of E2P)

5. Paste Type

Specify how you want to paste in the object to PowerPoint.  I typically recommend pasting as a PNG for charts and as an Enhanced Metafile for everything else to get the most consistent image quality.

NOTE: Due to a bug in PowerPoint’s VBA, E2P cannot currently paste in a linked chart or table and retain the formatting from the Excel spreadsheet. To get around this you can either paste your object as a picture or use an embedded paste (ie 11 Paste Shape).

6. Export?

This is a Yes/No toggle option to specify if you want to export the respective row into the PowerPoint presentation.  This option can be useful if you need to go back and update only a few objects and don’t want to re-export every row in your E2P table. 

Need help figuring out the exact position of a PowerPoint object?  Simply select an object inside of PowerPoint, go back to Excel and click the Dimension Finder ribbon button.  You will see a dialog pop up with the exact dimensions and name of the selected PowerPoint object.  The dialog floats so you can simply type in your dimensions into the E2P table.

In order to start your spreadsheet export to PowerPoint, click the Export Ribbon button.  This will bring up a dialog box that will allow you to tell E2P where the PowerPoint file is located on your computer.  Notice that there may already be a pre-populated file address in the dialog box.  This is because E2P remembers the file path you last used!  If you need to navigate to a different path, simply click the folder icon to bring up an Explorer dialog.

You can also specify if you want to open to PowerPoint as Read Only by clicking the Read Only? check box.  This will prevent you from accidentally overriding a previous presentation.

Once you have chosen your settings, simply click the Export button and wait for E2P to work it’s magic!

With the release of version 2.0, default settings were made available in order to all you to further customize how you use E2P. These settings are accessible via the dialog launcher button in the lower right-hand corner of the add-in’s Ribbon. Below are descriptions of each setting that you can tweak to your liking.

With the release of version 2.0, local spreadsheet specific settings are now available. The purpose of these settings is that they can be customized to each specific workbook that has an E2P worksheet installed inside the file. These settings are accessible via the dialog launcher button in the lower right-hand corner of the add-in’s Ribbon. Below are descriptions of each setting that you can tweak to your liking.

Slide Master & Slide Layout Settings

E2P will add additional slides to your presentations if the destination file does not have enough slides. As of version 2.2, you can now specify within the E2P Settings which Slide Master & Layout you want to use when additional slides are added.

The Slide Master within PowerPoint allows you to create and group slide layouts so you are not starting from scratch every time you add a new slide to your presentation. You can access the Slide Master View by navigating to your View tab and clicking the Slide Master button. From there you can create layouts and group them within Slide Masters.

E2P allows you to reference a specific layout within a specific Slide Master via the E2P Settings dialog box. In order to properly refer to your desired slide layout, you will need to determine the Slide Master index number and the Layout index number. The below images shows you how you can easily determine these reference numbers from the Home Tab within your PowerPoint file.

You can also easily determine the index numbers within the Slide Master View. This may be the more safe way of discovering the reference numbers as Slide Masters without any layouts will not show up inside the Home Tab view shown above. This could potentially throw off your count and cause you to reference the incorrect Slide Master index position.

Below is a screenshot of the default layouts when creating a brand new PowerPoint presentation. By default, E2P will insert blank slides or layout #7 within the first Slide Master. If your destination file has fewer than 7 layouts or multiple Slide Masters, you will need to adjust the settings, as E2P will stop it’s Export and notify you of the problem.

You’re in Excel, So Use Formulas

The great thing about add-ins in Excel is that you are almost limitless in your ability to incorporate Excel’s features.  So don’t hardcode everything in the E2P Table, use some formulas.  I typically try to group similar settings together with the table so I can use formulas that equal the row above.  That way if you need to make a change to a group (such as changing a position dimension), you will only need to change a single row instead of all of them.

For The Best Quality Images

There seem to be a select few pasting methods that produce the best quality image. Here are my recommendations:

• 02 Enhanced Metafile Picture – Named Ranges, Shapes, Tables, Grouped Shapes
• 06 PNG – Charts or Grouped objects including a chart (ie chart grouped with a textbox)
• 10 OLE Object– Embedded spreadsheet objects
• 11 Paste Shape – Embedded Chart or Table
• 12 Linked Picture – Clickable Image Opening Source File

For more information on this subject, check out my article The Best Way To Paste Special Excel Objects As PowerPoint Images

What Is The RTF (E2P) Pasting Option?

RTF stands for Rich Text Format and it allows you to paste text while maintaining formats such as color, size, bold, underline, etc… Unfortunately the built-in Microsoft RTF PasteSpecial does not do this very well when you are using it. It tends not to bring over the proper font size or only a single color when there are multiple colors used within the text.

As of version 2.5, E2P has its own built-in RTF PasteSpecial called 09 RTF (E2P). With this version of RTF pasting, you will be able to retain any unique formats within cell text.

Make your Named Ranges Look Professional

When copying Named Ranges, the pasted image will appear exactly how it appears in Excel.  This means that your Excel formatting needs to be impeccable in order to copy over a professional-looking image into your PowerPoint presentation.  Here are a few formatting tips I have developed over the years.

• Remove the spreadsheet gridlines (View Tab > Show Group > Gridlines)
• Leave white space around ranges with borders around them
• Maintain the original aspect ratio when resizing in PowerPoint

Automate The Export Functionality With VBA

With the release of version 2.25, you can now call the E2P Export functionality within your own VBA macros. To do this, you will need to make sure the E2P add-in is installed and your license code has been entered. Next, you can insert the following line of VBA code into your own macros.

Application.Run “‘E2P.xlam’!E2P_Export”, [ExportMethod], [ReadOnly], [Filepath], [ShowProgress]

All of the variable inputs are optional and if you forgo using them, you will be prompted with the normal Export userform to manually select your options.

The 4 variables can accept the following values:

• [ExportMethod] is a String variable and can accept one of 3 phrases:
  • New – Export to a brand new PowerPoint presentation file
  • Active” – Export to the active PowerPoint presentation file
  • “Path” – Export to a designated PowerPoint file
  • “New” – Export to a brand new PowerPoint presentation file
  • “Active” – Export to the active PowerPoint presentation file
  • “Path” – Export to a designated PowerPoint file
• [ReadOnly] is a Boolean variable and can accept either a TRUE or FALSE value based on if you would like to open the file as Read Only or not.
• [FilePath] is a string variable where you can insert the full file path of the PowerPoint file you wish to export to. If you are not using the ExportMethod “Path”, this input can be left out of your VBA code
• [ShowProgress] is a Boolean variable and can accept either a TRUE or FALSE value base on if you would like the “Export is Complete” message box to appear at the end of a successful E2P export. The main reason you may want to turn this option off would be in cases where you are calling the E2P_Export function multiple times within a single subroutine (ie exporting multiple views of a single dashboard)

Example #1

Sub RunE2P()
'PURPOSE: Use E2P to Export Excel data to pre-created PowerPoint file and open as Read Only

Application.Run "'E2P.xlam'!E2P_Export", "Path", True, "C:\Users\chris\Desktop\Sales Report.pptx"

End Sub

Example #2

Sub RunE2P()
'PURPOSE: Use E2P to Export Excel data to a new PowerPoint file

Application.Run "'E2P.xlam'!E2P_Export", "New"

End Sub

Instantly Download Example VBA Files (must be using E2P v2.4 or later)

I have created a few scenarios that you will most likely run into while trying to integrate some more advanced uses of E2P into your processes. These scenarios were asked by numerous companies, so I decided to make the examples downloadable for all E2P users. I have commented on every line of code so you can easily follow what the VBA code is doing.

• Export Multiple Views Of A Single Excel Dashboard (options to export to a new presentation or to an already created presentation)
• Export Multiple PowerPoint Presentations From A Single E2P Sheet 
• Export Multiple Pivot Charts From A Single Pivot Table (export to a new presentation)

Still Need Help? Ask Away!

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.