Consulting

Results 1 to 3 of 3

Thread: Formulas of an embedded excel in powerpoint don't work unless I double click them

  1. #1

    Formulas of an embedded excel in powerpoint don't work unless I double click them

    Hi guys,

    I found some similar threads on the internet, but they couldn't help a lot.

    I have an Excel Table embedded in PowerPoint. It has some formulas, which use the VLOOKUP function to show some values from another Excel file. The problem is that when the values in the original file are changed, the embedded table doesn't refresh (it shows the old values). When I double click the cells with the formulas they get refreshed. Is there some way to refresh the embedded table? The recalculation is set to automatic. But it seems that it doesn't get executed. When I go to "Data -> Edit Links -> Update Values" it updates them. But it doesn't do that automatically. With "Data -> Edit Links" I see the source file as a link. But when I go out of the excel table and I check in the PowerPoint it doesn't show to have some linked files.

    I coded a Macro that rewrites the formulas every time, so they get refreshed. There is a line for every cell simillar to this:

    ActivePresentation.Slides(2).Shapes(1).OLEFormat.Object.Sheets(1).Range("C4 ").Formula = "=VLOOKUP(L_BP_PROJEKTID,..........,COLUMN(........),FALSE)"

    The problem is that the structure of this table gets changed very often. And I have to change the Macro every time and add the new reference to the new cell.

    Is there a chance to write a Macro that will refresh the whole table? I think of something like either refreshing the whole excel sheet or writing a macro that goes through all the cells and activates every one of them. I tried with the command "activate" but it doesn't work.

    Many thanks for your help in advance!

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I created a Excel workbook and save it

    In PP Insert, Object, Browsed to the file, and checked Link

    That way they stay in sync and the PP object gets updated

    So, maybe if you put all the VLookup's etc. in the Excel and only had PP display the linked results it would work for you???
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Hi Paul,

    yes, you are right. This will solve the problem, but I need this to happen in the powerpoint. This is the requirement. Two files will be incovenient. But anyways, this was an old powerpoint file and it is some kind of a bug. I prepared a new powerpoint with an excel with the same formulas and way of embedding. And it works just fine. I just wondered if I can program a simpler code than mine, which will help, in case that this bug comes up again.


    But thanks anyway!

Tags for this Thread

Posting Permissions

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