Setting up a link from Powerpoint to Excel

I have a custom formatted table in PowerPoint which I want to link to a range in Excel, but as far as I can see my only option is to paste the Excel range data as a link. The problem with this is that it comes in with the formatting and row heights in Excel and I don't have the option of expanding the row heights without distorting the font, and that ruins the look of the table in PPT. I could try to compensate in Excel but then it wouldn't look right in Excel.

Is there a way to set up a link so that it flows into my already formatted table?

Thanks,
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomfarrarCommented:
You could follow the instructions here and then once the object was in PowerPoint, you could right click the table a format it the way you wanted to.

http://www.wikihow.com/Link-Excel-to-PowerPoint
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Tom, I'll check it out when I get to work tomorrow,
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
You could also do this by using a VBA macro in PowerPoint to grab the data from Excel but as I'm not sure if a programmatic solution was what you were looking for, I will wait to hear back before posting any code.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Jamie, thanks. VBA would be great. The only problem is that I have Office 2010 here at work in which you can't record a macro anymore, and I'm not very familiar with Powerpoint VBA syntax.

I have 2 tables in Powerpoint  which I want to link to 2 named ranges in Book1.xlsm, and 2 charts which I want to link to 2 named ranges in Book1.xlsm.

Usinf the named ranges MyRange1 through MyRange4, what VBA code would I use to link and then, going forward, update my PowerPoint file?

Thanks!
John
0
tomfarrarCommented:
Hi John - Probably a good idea to work with Jamie on a VBA solution.  If that doesn't prove out, maybe you could provide some sample tables of what you are talking about in PP.  I am having a hard time visualizing your issue.  - Tom
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Sounds like a plan, Tom. Thanks.
0
Jamie GarrochPowerPoint Consultant & DeveloperCommented:
Hi John. This first thing to consider when creating VBA that uses 2 or more Office apps is to decide which app is hosting the code. For example, do you want to export the range from Excel to a table in PowerPoint or do you want PowerPoint to import the range into a table from Excel.

In the code below, I've assumed you are hosting in Excel. This will export the specified named range from the specified sheet to a new presentation but you could modify the code to open an existing presentation and use an existing slide/table as per your needs.

Option Explicit

'----------------------------------------------------------------------------------
' VBA Macro to export a named Excel range to a table on slide in PowerPoint
'----------------------------------------------------------------------------------
' Copyright (c) 2015 YOUpresent Ltd.
' Source code is provide under Creative Commons Attribution License
' This means you must give credit for our original creation in the following form:
' "Includes code created by YOUpresent Ltd. (YOUpresent.co.uk)"
' Commons Deed @ http://creativecommons.org/licenses/by/3.0/
' License Legal @ http://creativecommons.org/licenses/by/3.0/legalcode
'----------------------------------------------------------------------------------
' Purpose : Exports a named Excel range to a table in a new PowerPoint presentation
' Hosting App : Excel
' Author : Jamie Garroch
' Date : 30JUL2015
' Website : http://youpresent.co.uk/
'----------------------------------------------------------------------------------

' Usage : RangeToPowerPointTable "Sheet1", "MYRANGE1"
Sub RangeToPowerPointTable(mySheet As String, myNamedRange As String)
  ' Excel part
  Dim oRange As Range, TableRows As Long, TableCols As Long
  Set oRange = Worksheets(mySheet).Range(myNamedRange)
  TableRows = oRange.Rows.Count
  TableCols = oRange.Columns.Count
  
  ' PowerPoint part
  ' Use late binding for portability (but without IntelliSense)
  ' To use Early Binding and IntelliSense:
  ' 1. Add a reference to the project for 'Microsoft PowerPoint XX Object Library'
  ' 2. Change declarations to the commented out types
  ' 3. Comment out the line Const ppLayoutBlank = 12
  Const ppLayoutBlank = 12
  Dim oPPT As Object ' PowerPoint.Application
  Dim oPres As Object ' Presentation
  Dim oSld As Object ' Slide
  Dim oShp As Object ' Shape
  Dim SlideW As Long, SlideH As Long
  
  ' Create an instance of PowerPoint, a new presentation, and then add a slide (you could open an existing file instead)
  Set oPPT = CreateObject("PowerPoint.Application")
  ' Create a new presentation
  Set oPres = oPPT.Presentations.Add(msoTrue) ' or to open an existing pres, use the next line instead
  'Set oPres = oPPT.Presentations.Open("FullPathAndFileName", msoFalse, msoFalse, msoTrue)
  SlideW = oPres.PageSetup.SlideWidth
  SlideH = oPres.PageSetup.SlideHeight
  Set oSld = oPres.Slides.Add(1, ppLayoutBlank)
  ' Add a centred table ocupying 90% of the slide
  Set oShp = oSld.Shapes.AddTable(TableRows, TableCols, SlideW * 0.05, SlideH * 0.05, SlideW * 0.9, SlideH * 0.9)
  
  ' Fill the table from the Excel range
  Dim rowCounter As Long, colCounter As Long
  For rowCounter = 1 To TableRows
    For colCounter = 1 To TableCols
      oShp.Table.Cell(rowCounter, colCounter).Shape.TextFrame.TextRange.Text = oRange(rowCounter, colCounter)
    Next
  Next
  
  ' Clean up
  Set oRange = Nothing
  Set oPPT = Nothing: Set oPres = Nothing: Set oSld = Nothing: Set oShp = Nothing
End Sub

Open in new window


For the chart part, I would create the chart in Excel and then export that as a PNG to PowerPoint if you want to protect the data but you could export the chart (and data) too as a native chart or a scalable Metafile image.

Here's an example:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28664971.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Jamie, I'll check out this solution and the one for the other question when I get to work later today,
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Jamie, I couldn't work with this because I couldn't step through it and it wouldn't run when I hit F5. But I could with your solution at the other link and it worked great.  

How do you debug the one you posted above?

Thanks,
John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft PowerPoint

From novice to tech pro — start learning today.