Martin_Ko
10-05-2017, 05:55 AM
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!
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!