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.
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.
After adding a new module, double click it and on the right hand side edit area we will add the VBA code.
The above in Copy/Paste format. 'SQL Declaration stuff Dim objRec Dim objConn Dim cmdString Dim dblTotalSales As Double 'Use this sub to identify the ID of the Textboxes on the presentation (CTRL+PAUSE breaks the Loop) Sub GetShapeID() Dim MyCount As Long On Error Resume Next For MyCount = 1 To 1000 Set objPresentaion = ActivePresentation Set objSlide = objPresentaion.Slides.Item(1) Set objTextBox = objSlide.Shapes.Item(MyCount) MsgBox "Object ID:" & MyCount & " VALUE=" & objTextBox.TextFrame.TextRange.Text Next MyCount End Sub Sub GetTotalSales() Set objRec = CreateObject("ADODB.Recordset") Set objConn = CreateObject("ADODB.Connection") DoEvents objConn.ConnectionString = "DSN=MYDBNAME;UID=accuser;PWD=$^#@$&^#$;" objConn.Open cmdString = " select SUM(SALESAMT) from V_S11ANDSERVCOMBINED_ALL where TRANDATE >='20160801' or TRANDATE <= '20160830' " Set objRec = objConn.Execute(cmdString) For Each x In objRec.Fields 'MsgBox (x.Value) dblTotalSales = (x.Value) Next objConn.Close 'Udate the textbox with Sales value returned from SQL Database Set objPresentaion = ActivePresentation 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 objTextBox.TextFrame.TextRange.Text = dblTotalSales End Sub
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.
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.
Sub Wait() Dim waitTime As Long Dim start As Long waitTime = 1800 start = Timer While Timer < start + waitTime DoEvents Wend End Sub
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.
Leave a comment if you have a question.
542 total views, 2 views today