+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

How to change range of linked worksheet object

  1. #1
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    336

    How to change range of linked worksheet object

    Hi All,

    As I said in topic is it possible in Power Point 2013 to change range of linked worksheet object.
    I select mouse right button ---> Linked Worksheet Object ---> Edit go to Excel but don't know how to change the range. Is it possible to change it or not?

    Thank you in advance.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: How to change range of linked worksheet object

    If you right click on the object you can select Linked Binary Worksheet Object -> Edit. That will bring up the sheet containing the current object. From there you can delete the current object and recopy and paste.

    A possible better way of doing this is from the Excel side. The way I do this is I have a "template" powerpoint presentation with all the "fixed" text and blank places where I want to put the charts, tables and variable text. I then use VB code to copy and paste from excel to the powerpoint.

    This is a lot faster process if you have multiple charts or data. With a link, what powerpoint does is open the excel spreadsheet, get the item, close the spreadsheet, go to the next link, open the spreadsheet ... So if you have 20 links it opens and closes the spreadsheet 20 times. Going the other way, you open the spreadsheet once and copy everything at once.

    If you are interested in trying this technique let me know. I have some sample code.
    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.

  3. #3
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    336

    Re: How to change range of linked worksheet object

    Definitely I'm interested so I would appreciate if you can help me.
    I have more than 20 Tables and over 20 Charts to update every week.
    On the other hand, I need to hide every week some columns from my Tables so could it affect macro to do something wrong?
    But, as you said the best option would be to start with this matter

    Thank you in advance.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: How to change range of linked worksheet object

    The attached spreadsheet is a partial spreadsheet of an actual report I do. Missing is the proprietary data.

    I have a PowerPoint "template." it is not a real template in the official sense of the word, it is a PowerPoint presentation with corporate graphics, fixed-text like slide headers, replaceable text, most notably "<< Date >>" and "<< WE >>" (Week Ending) and blank spaces where the charts and tables should go.

    The number of pages I have in this report is fixed. I do not create pages. In fact, I have a maximum of 15 model pages. I don't always use all 15, so I go back and manually edit the finished PPT by deleting the unused pages.

    The first part of the code replaces the replacable text on the title page with the publish date and the Week Ending date.

    Then I replace the text in the footer with the Week Ending date also.

    Then I go through the table provided in the sample. The table is a "map" that tells the program where to find the stuff to copy in the excel workbook, and where to put it in the PowerPoint presentation.

    Column A is the Excel Page that contains the material to be copied.

    Column B is the Excel Object. This is either a chart name, or a range of cells. I typed the ranges in manually because, for me, they are the same every week. However, if your range changes every week, you may want to replace these with the name of a dynamic range. The code should take either fixed cell references or dynamic cell references. I can give you a hand with named dynamic ranges if you need them.

    Column C is the Excel Type. This can be either Chart or Range. This tells the code which syntax to use when copying.

    Column D is the PowerPoint page to which the chart or range should be copied.

    Column E is not used by the code. It’s a short note for people to read.

    Columns F:I are the position and size of the objects in inches (the code might have to be modified for the metric system).
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    336

    Re: How to change range of linked worksheet object

    I don't understand this Excel file. I am trying to figure out how this works, but without success. Do you have any simple example of this?

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: How to change range of linked worksheet object

    Here is a working example and an expanded explanation.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    11-03-2014
    Location
    Belgrade
    MS-Off Ver
    2010
    Posts
    336

    Re: How to change range of linked worksheet object

    Thank you for this. I am checking this and let you know the final result when I finish my PPT.

  8. #8
    Registered User
    Join Date
    10-06-2016
    Location
    US Scranton
    MS-Off Ver
    MS Office 2013
    Posts
    3

    Re: How to change range of linked worksheet object

    When you paste the range into PowerPoint, choose Edit, Paste Special instead
    of Paste.

  9. #9
    Registered User
    Join Date
    09-06-2017
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    6

    Re: How to change range of linked worksheet object

    Quote Originally Posted by dflak View Post
    Here is a working example and an expanded explanation.
    it code is very smart, but could you help me change the copy picture to object, I'd like to make the hyperlink from data source file to ppt file

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,980

    Re: How to change range of linked worksheet object

    lekhathanh, you should not "hitchhike" on another post like this. Please open a new post.

    But to answer your question. The purpose of this code was so that charts and ranges do NOT link back to the workbooks because the workbooks take too long to update when data is linked back. If you need a live link, then make sure you specify that in your post.

  11. #11
    Registered User
    Join Date
    09-06-2017
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    6
    Hi dflak

    I really need the live link. Would you advise me
    Last edited by AliGW; 08-27-2018 at 11:48 AM.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    31,080

    Re: How to change range of linked worksheet object

    Start your own thread, please. Link to this one if you think it is relevant.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  13. #13
    Registered User
    Join Date
    09-06-2017
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    6

    Re: How to change range of linked worksheet object

    Thanks Ali

  14. #14
    Registered User
    Join Date
    09-06-2017
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    6

    Re: How to change range of linked worksheet object

    I had post to other thread

  15. #15
    Registered User
    Join Date
    09-06-2017
    Location
    Vietnam
    MS-Off Ver
    Office 2013
    Posts
    6

    Re: How to change range of linked worksheet object

    Quote Originally Posted by dflak View Post
    lekhathanh, you should not "hitchhike" on another post like this. Please open a new post.

    But to answer your question. The purpose of this code was so that charts and ranges do NOT link back to the workbooks because the workbooks take too long to update when data is linked back. If you need a live link, then make sure you specify that in your post.
    Hi dflak

    I had post a new thread but not any advise, can you help change your code to it can be copy from multi file excel to one file ppt

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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