+ Reply to Thread
Results 1 to 11 of 11

PowerPoint from Excel

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    PowerPoint from Excel

    The attached application takes ranges, charts and text in Excel and places them in a pre-defined PowerPoint Presentation.
    Attached Files Attached Files
    Last edited by dflak; 06-29-2017 at 01:11 PM. Reason: Put in new zip file with updated documentation
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: PowerPoint from Excel

    I am trying to test this and am getting "Runtime error 9: Subscript out of range" and I clicked debug and it is highlighting this line:

    Please Login or Register  to view this content.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: PowerPoint from Excel

    That's because the documentation was in error. It used to matter what you added in the Excel Sheet and Excel Object columns even if it the type was text. You caused me to take a second look and modify the code. Now it doesn't matter what you put in these two fields if the type is text. The new spreadsheet and documentation are now attached to the original post.

  4. #4
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: PowerPoint from Excel

    Just tried it and it really works! Are you God?

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: PowerPoint from Excel

    No, but we are on speaking terms .

    This is a refinement of something that I had to do for work. Our PowerPoint was linked to Excel ranges and charts. But for each link it opened the Excel Workbook, got the information and closed the Excel workbook. The book opened and closed slowly and sometimes we got errors by trying to reopen it before it was closed by the previous link. So we had to baby sit the process and click off the dialog boxes as they popped up. If we were lucky it ran all the way through in about 2 hours. With this method, it takes about 10 seconds.

    The one thing I can't do (yet) is add pages if they are needed. I'm stuck with a fixed template. That's for some future time.

  6. #6
    Registered User
    Join Date
    01-06-2016
    Location
    Baltimore, MD
    MS-Off Ver
    Standard 2010
    Posts
    21

    Re: PowerPoint from Excel

    Where / how did you learn to do that? This could literally save me hundreds of hours a year in copy / paste work.

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: PowerPoint from Excel

    Necessity is the mother of invention.

    We had a presentation that linked back to an Excel workbook. The Excel workbook is huge and takes about 45 seconds to a minute to open and about half that to close even without saving. We had dozens of links back to the workbook. For each link, PowerPoint opened the workbook, got the chart or table specified in the link, closed the workbook and went to the next link. It took about 1.5 hours to 2 hours to update the presentation and you had to babysit the application to answer the dialog box that said that the Excel workbook was already open and do you want to open it again.

    So, I looked at some examples, read some reference material, did some trial and error and then went back and cleaned up. We now produce a much larger presentation in about 30 seconds.

    As with many Excel-Other applications, you have to know whether it is better to push or to pull. In this case, pushing from Excel works better than pulling from PowerPoint. Elsewhere I have found it better to pull from Excel than to push from a database.

    It still can use some improvements such as making sheets "from scratch" using a real template rather than working with a "hard formatted" presentation. That improvement will probably not be made by me since I don't need it .

  8. #8
    Registered User
    Join Date
    01-06-2016
    Location
    Baltimore, MD
    MS-Off Ver
    Standard 2010
    Posts
    21

    Re: PowerPoint from Excel

    Thank you for responding. I manage our group's investment management reporting. We are using large excel workbooks, one for each client, that are updated quarterly. Our reports are generated from PowerPoint presentations that contain charts with the excel information embedded in the chart. So to update each chart every quarter, we right-click on the chart in PowerPoint and choose "Edit Data...", which opens an excel spreadsheet. We then copy and paste the values from the excel workbook into the embedded excel spreadsheet. We have roughly 15 charts in each PowerPoint presentation that are each updated like this manually every quarter for almost 100 clients. So right now the excel workbook and PowerPoint are not "linked" as you describe. We are manually copying and pasting data.

    If you would be able to point me in the direction of reference material you used, I would greatly appreciate it. I have rudimentary VBA skills, but seeing this amazing application you built, I think I am ready to start taking courses and become more fluent in VBA.

    Thanks so much again. Jake

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: PowerPoint from Excel

    I found this reference very handy: https://msdn.microsoft.com/en-us/vba/vba-powerpoint.

    A couple of hints: Give your charts sensible names. You can use a fixed range of cells or you can use a named dynamic range. I have not tested it, but it should work: you can use an Excel Table name - you may have to reference it as Table_Name[#All].

    In my case, I have 5 fixed pages and a variable number of pages that give metrics for models that meet a certain criteria. I added extra pages both thin the workbook and the presentation and delete the pages that don't have models.

  10. #10
    Registered User
    Join Date
    01-06-2016
    Location
    Baltimore, MD
    MS-Off Ver
    Standard 2010
    Posts
    21

    Re: PowerPoint from Excel

    Thank you so much! Jake

  11. #11
    Forum Contributor
    Join Date
    02-09-2014
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    189
    Quote Originally Posted by dflak View Post
    The attached application takes ranges, charts and text in Excel and places them in a pre-defined PowerPoint Presentation.
    Still smashing it out!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-06-2017, 07:08 AM
  2. Replies: 0
    Last Post: 08-23-2016, 09:43 AM
  3. Replies: 1
    Last Post: 06-09-2016, 12:05 PM
  4. Replies: 6
    Last Post: 08-18-2015, 06:39 AM
  5. Excel/PowerPoint VBA teaser: How to change data labels in PowerPoint chart by VBA?
    By lukelucky in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-12-2015, 11:47 AM
  6. VBA Transfer Range from Excel to Powerpoint (New or Existing Powerpoint Presentation)
    By ThePeoplesClub in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2014, 02:52 PM
  7. Replies: 13
    Last Post: 01-11-2013, 04:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1