+ Reply to Thread
Results 1 to 17 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
    341

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

    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
    341

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

    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
    341

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

    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
    341

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

    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
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,272

    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

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,070

    Re: How to change range of linked worksheet object

    lekhathanh, you said you have posted your own thread for this. Please be a little patient, Im sure a member will respond soon.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  17. #17
    Registered User
    Join Date
    06-06-2019
    Location
    Colorado
    MS-Off Ver
    Windows 10
    Posts
    5

    Re: How to change range of linked worksheet object

    I was curious about this also, I can't get it down either.

+ 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: 08-26-2013, 05:04 PM
  2. How to duplicate a worksheet and change the linked cells for each sheet.
    By henryBukowski in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2013, 05:59 AM
  3. Change item source for linked excel object by VBA
    By koticphreak in forum Word Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2013, 12:44 PM
  4. How do you use the Worksheet property of the Range object?
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-09-2013, 09:49 PM
  5. Replies: 1
    Last Post: 07-19-2013, 04:51 AM
  6. Worksheet change event for linked value
    By roli001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2013, 06:24 PM
  7. Worksheet change event not working on linked cells
    By Mac10 in forum Excel General
    Replies: 1
    Last Post: 09-21-2010, 03:13 AM
  8. [SOLVED] Range Question / error 1004: method Range of object Worksheet has failed
    By Paul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 10:06 AM

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