Update Excel Data automatically in a PPSX - note: not a linked file

Question

I am running PowerPoint 2016 on a computer that has been configured as a Kiosk. The file is a PPSX and one slide is showing a daily countdown to a specific date in the future. I have inserted an Excel Spreadsheet into the slide with 3 cells populated:

	A	B
1	=DATE()	03/31/2022
2	=DAYS(B1,A1)

Is there any way to automatically update the field in Cell A1 so that it always shows the current date and the formula result in Cell A2 is always current. I am going in daily to update do not want to have to continue to do that for the next 200+ days. Any suggestions would be appreciated. Thank you.

Answer 1

Try this ... it's working here:

Insert a new VBA module into your file and paste in this code:

Sub OnSlideShowPageChange(ByVal Wn As SlideShowWindow) 

    ActivePresentation.UpdateLinks 

End Sub 

Add an activex control to the first slide (drag it off the slide so it doesn't show).

Make sure the Excel file is saved to the same folder as the PPTM.

Select the cell with the data you want to update in Excel, choose Copy then switch to PPT

Choose PasteSpecial, checkmark LINK, then choose to paste an Excel object.

Once it's pasted, you can enlarge it, move it around etc, but to change the font formatting, do it in Excel before copy/pasting.

Duplicate the slide. Set both slides to transition automatically, after whatever interval you want the update to happen at.

Close the Excel file, start the show in Kiosk mode.

Answer 2

Thank you, yes, VBA may be the way to go. I did some looking around for the last two days and could not find a solution so that is why I posted the question in the community. Any help would be appreciated.

Answer 3

Unfortunately for what you're after, Excel data linked to PPT only updates when a) the PPT file opens or b) when you manually or programmatically (ie, via macro) force the link to update.

You can, I think, accomplish what you're after with a bit of VBA set to trigger whenever a particular slide appears, but VBA isn't always acceptable in all cases. Let us know if it is, and one of us might be able to work something up for you.

Answer 4

Unfortunately, that does not help and I don't think you understood my question clearly. I do not want to insert a Chart. The PowerPoint file is a PowerPoint show(PPSX) and is running 24/7 at my place of business. I have one slide that is doing a daily countdown to an upcoming event; i.e. "204 Days until...." and the value of "204" is based on a calculation of the days between the current date and the date of the even in the future. I do not want to have to open and close the file on a daily basis to update this field, I just want the current date in the embedded Excel spreadsheet to update every day. Can this be done? Is there a way to refresh a PowerPoint slide on a daily basis, i.e. at 12:01AM white it is running?

Answer 5

Hi, Opal4_3.

I'll help you.

For an AUTOMATIC upgrade, you will need to link an entire spreadsheet or data section from Excel to PowerPoint. When Excel data is not linked to your PowerPoint presentation, the spreadsheet does not send automatic updates to PowerPoint.

You can see this more detailed information in:

https://support.microsoft.com/en-us/office/inse...

The video below is a tutorial on how to link the data for an automatic update:

https://youtu.be/cP6EuYhWHoo

I hope this helps.