Fetch data with PowerPoint from a SQL Server into a PowerPoint Textbox

9

Fetching SQL data from a Microsoft PowerPoint presentation can be handy if you need make a sales display board that, display daily sales, highest invoice of the day or any other general  information.

In this post I show you how. I don’t take credit for all of the code. I simply used snippets from all over and added them together.

First things first.
You need to enable developer mode in PowerPoint just like you would do it in excel by selecting the Developer checkbox in the customize ribbon setup.

Click File –> Options –> Customize Ribbon and select the Developer checkbox.

12 3 4

Open the VBA IDE by clicking Visual Basic from the Developer ribbon. Right-Click the VBA Project and insert an new module as shown below.

5

After adding a new module, double click it and on the right hand side edit area we will add the VBA code.

7

 

Before running the code we need to add a textbox to the Presentation and we need to tell the code which TextBox on the presentation needs to be updated by providing the ID of the textbox.
Unfortunately PowerPoint was not really designed around managing Textboxes via the VBA GUI so it doesnt really show the Textbox ID anywhere. So therefore I created a Sub that will loop through the first 1000 textboxes and display their IDs using a message box.

After Adding a textbox put some unuiqe text into it so that its easy to identify, run the GetShapeID Sub once you know what the ID is then update the below code
Set objSlide = objPresentaion.Slides.Item(1) ‘(1) = The Object ID Find the ID using the Sub GetShapeID
Set objTextBox = objSlide.Shapes.Item(1) ‘(1) = The Object ID Find the ID using the Sub GetShapeID

 

Now simply run the code and the textbox will be updated.

6

Not going into too much detail, but you can also add a wait loop to update the dashboard on a regular basis with the below code.

Create a loop which calls the wait, Dont query the DB too often because you will cause problems.

 

Below is a sample Sales Dashboard which I created for the company I work for.

9

 

Have Fun!

Leave a comment if you have a question.




 

1,964 total views, 6 views today

Leave a Reply

Your email address will not be published. Required fields are marked *