How to extend the functionality of PowerPoint text box?

Question

Hi!

I am very new to developing PowerPoint apps. Please bear with me.

I would like to extend the functionality of a PowerPoint textbox such that it can link to an Excel sheet and auto-update the text content. For example, in a text box "Annual revenue of year XX is YY USD", XX and YY are linked to specific cells in an Excel workbook. Users should be able to add such an element to their PowerPoint slide, edit and format it like a regular textbox, and link certain parts of the text content to Excel.

• Are there any existing solution that can achieve this?
• If not, how can I build this? Are there any documentation/APIs/tutorials that I should check out?
• At a high-level idea, how shall I implement it? Can I add such functionality to the textbox element or do I need to build a custom element from scratch? How do I establish connections between PowerPoint and Excel?

Thanks a lot!

Answer 1

This is an interesting project. The first thing to note is that add-ins can only run in PowerPoint in edit mode. So, if you were hoping to see the values in the text box change in real time in presentation mode, that is not possible. The closest you could come to that would be to have the presenter press a button in the add-in to sync the data just before going into presentation mode.

Microsoft is working on techniques, based on Fluid Framework that would make it somewhat easier to carry out your scenario, but that is a long way into the future.

In the meantime, I suggest you explore the following as a strategy (which I have not tried, but I think should work):

1. In your JavaScript in the PowerPoint add-in, use Microsoft Graph to open and get data from an Excel workbook stored in OneDrive or SharePoint. It would have to be a workbook to which the signed-in PowerPoint user has Read permission.
2. Then your JavaScript constructs the text you want to appear in the text box, plugging in the data from Excel where you want it.
3. Then your code sets the text as the content for the text box.
4. The above code could be triggered by either a button on a task pane or an Add-in Command.

Answer 2

Hi Rick,

Thanks so much for the reply!

Yes, it is okay that the add-in can only run in PowerPoint edit mode!

I took a look at the approach you recommended. However, in this case, how should the user write the text content of the textbox and establish the link to Excel?

Ideally, we can enable the following user flow.

1. User creates a textbox element (or a special textbox element that we build).
2. User opens the special edit mode of the textbox in a pop-up window (maybe through double-clicking).
3. User writes the content in a special syntax. For example, "Annual revenue of year {'sheet1'!A1} is {'sheet1'!B1} USD".
4. When the popup window is closed, PowerPoint shows the textbox with data from Excel filled in. For example, "Annual revenue of year 2022 is 1,000,000 USD".
5. It is fine if the user needs to take an extra step to refresh the data.

Also, if the user is not using OneDrive or SharePoint, how can we get data from Excel to PowerPoint?

1. To the best of my understanding, if you copy data from Excel and paste it into PowerPoint as a "Microsoft Excel Worksheet Object." The link can be established locally without something like OneDrive. Can we do something similar?
2. A Thinkcell element can also link to Excel. If you link a Thinkcell element (on PowerPoint) to an Excel cell, both elements will be assigned the same object ID. If both the PowerPoint and Excel files are opened, the Thinkcell element in the PowerPoint file will be updated. Can we do something similar?

Sorry, though I tried very hard to dig into Office documentation, I still have a hard time understanding how things work.

Thank you!

Answer 3

Office Web Add-ins are web applications, so they have the same restrictions as web applications. With only a few exceptions, such as saving cookies, they cannot access the file system of the computer. So, an add-in can't open and read an Excel file from the file system, or even read a directory listing of any folder on the computer.

I'm not familiar with Thinkcell, but it seems to be a desktop application, not a web application, so it would have access to the file system.

But add-ins can get access to files stored in OneDrive or SharePoint. If you don't use OneDrive or SharePoint, then you can't use Microsoft Graph to get the file and its contents. You would have to reinvent the wheel: either develop a system for storing the Excel files, opening them, and reading the contents, or develop a desktop application like Thinkcell.

I don't know what you mean by "special textbox element that we build" or "special edit mode".

Once you've got the Excel data into variables year and total, you simply get a reference to the textbox and write to the textbox.textFrame.textRange.text property.

const textbox = shapes.addTextBox("Hello!", {  
      left: 100,  
      top: 300,  
      height: 300,  
      width: 450  
    });  
    textbox.textFrame.textRange.text = "Annual revenue of year " + year + " is " + total = " USD.";  

Hope this helps.