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

 

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.

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.

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.

9

 

Have Fun!

Leave a comment if you have a question.




 

569 total views, 1 views today