Auto populate data from database into powerpoint

[Julie]

Does anyone know if Powerpoint can pull in data from an
Access or SQL database to automatically populate "fields"
in a Powerpoint presentation?

 

[Steve Rindsberg]

Does anyone know if Powerpoint can pull in data from an

Access or SQL database to automatically populate "fields"
in a Powerpoint presentation?

On its own, I don't think so. With a push from VBA, yes.

We have a Merge add-in for PowerPoint that does this, although it uses
Excel, CSV or tab delimited data as its source files. You might want to
have a look at the info on it at www.pptools.com (there's also a free demo
available if it looks like something that'd work for you)

It's also possible to create links from Excel cells to PPT so that text, for
instance, can change in the PPT when you edit it in the linked XLS file(s)

 

[Kurt]

Julie,
There one addon that can do that, online or offline presentations, linked to
databases.
Have a look at www.take-off.as/datapoint
Kurt.

 

[John Langhans [MSFT]]

[CRITICAL UPDATE - If you are using Office 2003, you should install this
update as soon as possible. From PowerPoint, choose "Help -> Check for
Updates".]

Hello Julie,

PowerPoint does not provide that level of integration with data sources
(such as Access) without resorting to programming.

If you (or anyone else reading this message) thinks that is important for
PowerPoint to provide built in support for consuming, viewing and/or
interacting with external data (without having to resort to VBA or
add-ins), don't forget to send your feedback to Microsoft at:

http://register.microsoft.com/mswish/suggestion.asp

As with all product suggestions, it's important that you not just state
your wish but also WHY it is important to you that your product suggestion
be implemented by Microsoft. Microsoft receives thousands of product
suggestions every day and we read each one but, in any given product
development cycle, there are only sufficient resources to address the ones
that are most important to our customers so take the extra time to state
your case as clearly and completely as possible.

IMPORTANT: Each submission should be a single suggestion (not a list of
suggestions)

John Langhans
Microsoft Corporation
Supportability Program Manager
Microsoft Office PowerPoint for Windows
Microsoft Office Picture Manager for Windows

For FAQ's, highlights and top issues, visit the Microsoft PowerPoint
support center at: http://support.microsoft.com/default.aspx?pr=ppt
Search the Microsoft Knowledge Base at:
http://support.microsoft.com/default.aspx?pr=kbhowto

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Brian Reilly, MS MVP]

Julie,
Sure it can with VBA using DAO or ADO. There are some examples in the
MSKB. Or post back and I can post an example that we've written. I
can't get to it just right now.

Brian Reilly, PowerPoint MVP

 

[Jan Il]

Hi Brian!

Julie,
Sure it can with VBA using DAO or ADO. There are some examples in the
MSKB. Or post back and I can post an example that we've written. I
can't get to it just right now.

Please excuse my popping in, but, as I work mostly with both Access and
PowerPoint, I would truly appreciate it if you would please post an example
of what you folks have written on this process when you have time. I've
also sent MS a 'wish' for their list on this subject as John suggested. It
really is a very important subject for me in my scope of work, as it may be
for many others, and making such a process easier between the two programs
would be a great time saver.

Jan

 

[Brian Reilly, MS MVP]

Hi Jan,
You already have this code. It's on your PowerPoint Live CD. But here
goes again.

It worked with a database that we already had existing that took the
value from a combo box on change event that listed customers, then
looked up the projects they had in the database and listed them on the
PPT page in a text box. Very simple but all the core code you need to
get to and from Access from PowerPoint.

Brian Reilly, PowerPoint MVP


Option Explicit
Private Sub cboCustomer_Change()
On Error GoTo Err_Handler
Dim strSql As String
Dim AdoCnn As ADODB.Connection, AdoRs As ADODB.Recordset

If IsNull(Me.cboCustomer.Value) Then Exit Sub
If Trim(CStr(Me.cboCustomer.Value)) = "" Then Exit Sub

'Get a connection to database
Set AdoCnn = GetDatabaseConnection()
If AdoCnn Is Nothing Then
Exit Sub
End If
AdoCnn.Open

strSql = "select [Project Name] from [tblProject] where [Customer
ID]=" & Me.cboCustomer.Value
'Instantiate ADODB Recordset object
Set AdoRs = New ADODB.Recordset
'Open Recordset
AdoRs.Open strSql, AdoCnn, adOpenForwardOnly, adLockOptimistic
Me.lstProjects.Clear

'Populate Projects listbox
Do While Not AdoRs.EOF
Me.lstProjects.AddItem AdoRs![Project Name]
AdoRs.MoveNext
Loop
'Close Recordset object
AdoRs.Close

'Close ADODB Connection Object
AdoCnn.Close

Set AdoRs = Nothing
Set AdoCnn = Nothing

Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation
End Sub

Private Sub cboCustomer_DropButtonClick()
On Error GoTo Err_Handler
Dim strSql As String
Dim AdoCnn As ADODB.Connection, AdoRs As ADODB.Recordset

'Check if customer dropdown is already populated
If Me.cboCustomer.ListCount > 0 Then Exit Sub

'Get a connection to database
Set AdoCnn = GetDatabaseConnection()
If AdoCnn Is Nothing Then
Exit Sub
End If
AdoCnn.Open

strSql = "select [Customer ID], [Cust Name] from [tblCustomer]
order by [Cust Name]"
'Instantiate the ADODB Recordset object
Set AdoRs = New ADODB.Recordset
'Open Recordset
AdoRs.Open strSql, AdoCnn, adOpenForwardOnly, adLockOptimistic
Me.cboCustomer.Clear

'Populate the first value (blank) of the Customer dropdown
Me.cboCustomer.AddItem "0"
Me.cboCustomer.Column(1, Me.cboCustomer.ListCount - 1) = ""
'Populate the Customer dropdown with the list of values form the
database table
Do While Not AdoRs.EOF
Me.cboCustomer.AddItem AdoRs![Customer ID]
Me.cboCustomer.Column(1, Me.cboCustomer.ListCount - 1) =
AdoRs![Cust Name]
AdoRs.MoveNext
Loop
'Close Recordset Object
AdoRs.Close

'Close the Connection object
AdoCnn.Close

Set AdoRs = Nothing
Set AdoCnn = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation
End Sub

Private Function GetDatabaseConnection() As ADODB.Connection
Dim dbPath As String
Dim AdoCnn As ADODB.Connection

Set GetDatabaseConnection = Nothing
On Error GoTo Err_Handler

dbPath = ActivePresentation.Path
If VBA.Right(VBA.Trim(dbPath), 1) <> "\" Then
dbPath = dbPath & "\"
End If
dbPath = dbPath & "CustomerSolutions.mdb"

Set AdoCnn = New ADODB.Connection
AdoCnn.Provider = "Microsoft.JET.OLEDB.4.0"
AdoCnn.Properties("Data Source") = dbPath
AdoCnn.Properties("Jet OLEDBatabase Locking Mode") = 1
AdoCnn.CursorLocation = adUseServer

Set GetDatabaseConnection = AdoCnn
Exit Function
Err_Handler:
MsgBox Err.Description, vbInformation, "Get Database Connection"
End Function

 

[Jan Il]

Hi Brian

Hi Jan,
You already have this code. It's on your PowerPoint Live CD. But here
goes again.

I kinda had a feeling that's what you'd say..;-) But, wasn't really sure if
it was the same one you were talking about here.

It worked with a database that we already had existing that took the
value from a combo box on change event that listed customers, then
looked up the projects they had in the database and listed them on the
PPT page in a text box. Very simple but all the core code you need to
get to and from Access from PowerPoint.

Thank you very much, Brian.

Cheers,
Jan

Option Explicit
Private Sub cboCustomer_Change()
On Error GoTo Err_Handler
Dim strSql As String
Dim AdoCnn As ADODB.Connection, AdoRs As ADODB.Recordset

If IsNull(Me.cboCustomer.Value) Then Exit Sub
If Trim(CStr(Me.cboCustomer.Value)) = "" Then Exit Sub

'Get a connection to database
Set AdoCnn = GetDatabaseConnection()
If AdoCnn Is Nothing Then
Exit Sub
End If
AdoCnn.Open

strSql = "select [Project Name] from [tblProject] where [Customer
ID]=" & Me.cboCustomer.Value
'Instantiate ADODB Recordset object
Set AdoRs = New ADODB.Recordset
'Open Recordset
AdoRs.Open strSql, AdoCnn, adOpenForwardOnly, adLockOptimistic
Me.lstProjects.Clear

'Populate Projects listbox
Do While Not AdoRs.EOF
Me.lstProjects.AddItem AdoRs![Project Name]
AdoRs.MoveNext
Loop
'Close Recordset object
AdoRs.Close

'Close ADODB Connection Object
AdoCnn.Close

Set AdoRs = Nothing
Set AdoCnn = Nothing

Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation
End Sub

Private Sub cboCustomer_DropButtonClick()
On Error GoTo Err_Handler
Dim strSql As String
Dim AdoCnn As ADODB.Connection, AdoRs As ADODB.Recordset

'Check if customer dropdown is already populated
If Me.cboCustomer.ListCount > 0 Then Exit Sub

'Get a connection to database
Set AdoCnn = GetDatabaseConnection()
If AdoCnn Is Nothing Then
Exit Sub
End If
AdoCnn.Open

strSql = "select [Customer ID], [Cust Name] from [tblCustomer]
order by [Cust Name]"
'Instantiate the ADODB Recordset object
Set AdoRs = New ADODB.Recordset
'Open Recordset
AdoRs.Open strSql, AdoCnn, adOpenForwardOnly, adLockOptimistic
Me.cboCustomer.Clear

'Populate the first value (blank) of the Customer dropdown
Me.cboCustomer.AddItem "0"
Me.cboCustomer.Column(1, Me.cboCustomer.ListCount - 1) = ""
'Populate the Customer dropdown with the list of values form the
database table
Do While Not AdoRs.EOF
Me.cboCustomer.AddItem AdoRs![Customer ID]
Me.cboCustomer.Column(1, Me.cboCustomer.ListCount - 1) =
AdoRs![Cust Name]
AdoRs.MoveNext
Loop
'Close Recordset Object
AdoRs.Close

'Close the Connection object
AdoCnn.Close

Set AdoRs = Nothing
Set AdoCnn = Nothing
Exit Sub
Err_Handler:
MsgBox Err.Description, vbInformation
End Sub

Private Function GetDatabaseConnection() As ADODB.Connection
Dim dbPath As String
Dim AdoCnn As ADODB.Connection

Set GetDatabaseConnection = Nothing
On Error GoTo Err_Handler

dbPath = ActivePresentation.Path
If VBA.Right(VBA.Trim(dbPath), 1) <> "\" Then
dbPath = dbPath & "\"
End If
dbPath = dbPath & "CustomerSolutions.mdb"

Set AdoCnn = New ADODB.Connection
AdoCnn.Provider = "Microsoft.JET.OLEDB.4.0"
AdoCnn.Properties("Data Source") = dbPath
AdoCnn.Properties("Jet OLEDBatabase Locking Mode") = 1
AdoCnn.CursorLocation = adUseServer

Set GetDatabaseConnection = AdoCnn
Exit Function
Err_Handler:
MsgBox Err.Description, vbInformation, "Get Database Connection"
End Function