[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Inserting Dynamic Text into Powerpoint

Posted on 2016-11-01
16
Medium Priority
?
739 Views
Last Modified: 2016-11-03
Hi,
I've setup a fairly simple Powerpoint slideshow to use as digital signage in several retail outlets. There are several pieces of text which need to vary by location so I'm looking for an easy way to link this rather than have a different version of the presentation for each outlet.
I'm fully conversant in VBA but not Powerpoint particularly so does anyone have an example of how I might read a small text/csv file at the point of loading the presentation and update several text boxes please?
Any other ideas would be most welcome!
Thanks.
0
Comment
Question by:nigelr99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 12

Accepted Solution

by:
Jamie Garroch earned 2000 total points
ID: 41868242
This should get you going:

Option Explicit

' ===============================================================================
' PowerPoint VBA Macro by YOUpresent.co.uk
' Author:   Jamie Garroch 01NOV2016
' Purpose:  Loads text from a CSV text file and replaces the text in the
'           referenced shapes.
' CSV format:
'   [slide index],[shape name],[text]
' Example:
'   1,Rectangle 1,This is some new text
'   2,Rectangle 3,This is some other new text
' Note:     shape names may not be uniquie so shape ID's are better or use
'           the shape's .Tag property to manage shapes as this property can't be
'           accessed via the UI.
' ===============================================================================
Sub LoadTextFromFile()
  ' Source text file for the new text
  Const TextFile = "C:\Temp\textboxes.txt"
  Dim FileLine As String
  Dim LineItems As Variant
  Open TextFile For Input As #1
  Do Until EOF(1)
    Line Input #1, FileLine
    LineItems = Split(FileLine, ",")
    ' If the line has the required 3 component parts, process it
    If UBound(LineItems) - LBound(LineItems) = 2 Then
      With ActivePresentation
        On Error GoTo errorhandler ' In case the slide or shape doesn't exist
        .Slides(CInt(LineItems(0))).Shapes(LineItems(1)).TextFrame.TextRange.Text = LineItems(2)
        On Error GoTo 0
      End With
    End If
  Loop
  Close #1
Exit Sub
errorhandler:
  Debug.Print "Couldn't find " & LineItems(1) & " on slide " & LineItems(0)
  Err.Clear
  Resume Next
End Sub

Open in new window


To automatically run code when opening a presentation, you'll need to use application events as per this article I wrote:

https://www.experts-exchange.com/articles/17410/Initializing-PowerPoint-Events-with-VBA-and-Ribbon-XML.html
0
 

Author Comment

by:nigelr99
ID: 41868274
Perfect.. will trial shortly..
Many thanks
0
 

Author Comment

by:nigelr99
ID: 41868519
Works perfectly and saved me lots of time so much appreciated!
Cheers !!
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 

Author Comment

by:nigelr99
ID: 41869729
Just one query, everything working brilliantly except when I save as macro-enabled-show to distribute - is there something I'm missing (using App_SlideShowBegin event) ?
0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41869769
Ahh! I suspect that if you're saving as a .ppsm that the ribbon UI won't get loaded so the initiation of the application event handler won't get called as the ribbon's onLoad event won't fire. If this is the case, you could use the legacy pseudo-event instead. I wrote another article here on the pros and cons of psuedo events in PowerPoint:

http://youpresent.co.uk/powerpoint-application-events-in-vba/

I guess you are distributing to automate the start of the slide show? If that is the case, another possible solution would be to save as a .pptm to ensure the ribbon event fires and then within your initialisation code, automatically start the slide show with:

ActivePresentation.SlideShowSettings.Run

Open in new window


That would force the slide show to start automatically when opening the .pptm file.
0
 

Author Comment

by:nigelr99
ID: 41869958
Thanks, I've tried the following but slideshow doesn't start? Sorry, not familiar with Powerpoint events etc.

Public WithEvents App As Application

Private Sub App_PresentationOpen(ByVal Pres As Presentation)
    ActivePresentation.SlideShowSettings.Run
End Sub

Private Sub App_SlideShowBegin(ByVal Wn As SlideShowWindow)
    Populate_Text_From_File
End Sub

Open in new window

0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41869991
I just spotted that both tables in that article state column 1 as "Psuedo Event". The second table should be "Application Event". Because you cannot use the ribbon to initialise the application events in a .ppsm file, what I was suggesting was to use one of the 4 [very limited] legacy psuedo-events that do not require initialisation. They are:

OnSlideShowPageChange
OnSlideShowTerminate
OnSlideShowNextBuild
OnSlideShowPreviousBuild

I would use the first one to trigger your code and set a flag to prevent it retriggering like so:

Option Explicit

Public bInitApp As Boolean

Sub OnSlideShowPageChange(ByVal Wn As SlideShowWindow)
  Debug.Print "Slide Show Page Change"
  If Not bInitApp Then
    ' Do your text box stuff here
    Debug.Print "Stuff done"
  End If
  bInitApp = True
End Sub

Sub OnSlideShowTerminate(ByVal Wn As SlideShowWindow)
  Debug.Print "Slide Show Terminated"
  bInitApp = False
End Sub

Open in new window

0
 

Author Comment

by:nigelr99
ID: 41870057
Great thanks - can you just clarify, are we now talking about a pptm or ppsm file. I assumed the ppsm should start the slide show automatically by it's nature so the OnSlideShowPageChange should fire at the start? It doesn't appear to though.
0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41870134
Yes, I suggested the pseudo events as you wanted to have a .ppsm file. It could be that your macros are being blocked by the trust centre. I always digitally sign mine so I have this set in Options / Trust Center / Trust Center Settings / Macro Settings and with a Authenticode root CA signed .ppsm it works:

PowerPoint Trust Centre Settings for Macros
But you probably need the last/fourth (least secure) option. You can leave the second Developer Macro Settings option unticked as that's only needed when you use VBA to manipulate the VBA project code programmatically.
0
 

Author Comment

by:nigelr99
ID: 41870266
Thanks again - already had the trust centre covered. It seems I might be on a wild goose chase is any case as just read the the Powerpoint viewer does not support any code of any sort which leaves me high and dry with the whole idea - is this correct? (I'm using the viewer on Intel stick pcs at each location)
0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41870331
Oh no. That is indeed a dead end! As per the last sentence in the Details section of this Microsoft PowerPoint Viewer download article, macros are still not supported by this free application.

Thinking out loud, I'm wondering if there is a solution using VBScript. I'm no expert but I'm pretty sure you could develop a script that runs under Windows Script Host to populate your text boxes in a target pptx file.
0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41870382
Out of curiosity, I'm just prototyping a VBScript - give me a few minutes.
0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41870400
Ta da. Copy this to a .vbs file, change the constants for the text file and presentation respectively and then run the VBS. I had to strip out error trapping to save time so it's not yet fully robust but I tested it and it works. You can then distribute a .pptx file (no VBA code) and the VBS/TXT files to modify it before entering slide show mode.

' VBScript file to swap text in a PowerPoint file with that specified in a separate text file.
' Author : Jamie Garroch of YOUpresent Ltd. (http:\\youpresent.co.uk)

' Set the location and file name of the CSV-formatted text file 
Const TextFile = "C:\temp\textboxes.txt"
' Set the location and file name of the PowerPoint slide show file 
Const PresFile = "C:\temp\ReplaceText.pptx"

Dim oPPT 	' Object : PowerPoint Application
Dim oPres ' Object : Presentation
Dim oSld 	' Object : Slide
Dim oShp 	' Object : Shape
Dim oFSO 	' Object : File System
Dim oFile ' Object : Text File Stream
Dim FileLine
Dim LineItems

Set oFSO = CreateObject("Scripting.FileSystemObject")

If Not oFSO.FileExists(TextFile) Then
	MsgBox "Cannot find the input text file:" & vbCrLf & vbCrLf & TextFile,vbOKOnly+vbCritical, "Text File Missing"
	WScript.Quit
End If

If Not oFSO.FileExists(PresFile) Then
	MsgBox "Cannot find the input PowerPoint file:" & vbCrLf & vbCrLf & PresFile,vbOKOnly+vbCritical, "PowerPoint File Missing"
	WScript.Quit
End If

Set oPPT = CreateObject("PowerPoint.Application")
oPPT.Presentations.Open PresFile
Set oPres = oPPT.ActivePresentation

' Swap the text
Set oFile = oFSO.OpenTextFile(TextFile)
Do Until oFile.AtEndOfStream
  FileLine = oFile.ReadLine
  LineItems = Split(FileLine, ",")
  ' If the line has the required 3 component parts, process it
  If UBound(LineItems) - LBound(LineItems) = 2 Then
    With oPres
      .Slides(CInt(LineItems(0))).Shapes(LineItems(1)).TextFrame.TextRange.Text = LineItems(2)
    End With
  End If
Loop
  
oPres.SlideShowSettings.Run

Open in new window

0
 

Author Comment

by:nigelr99
ID: 41871909
You're really going the extra mile here and I appreciate it immensely! This works perfect until I try it on a pc with only the viewer installed and then script fails when trying to create "powerpoint.application" object. I don't suppose there's some sort of powerpoint-viewer object you're aware of instead?
0
 
LVL 12

Expert Comment

by:Jamie Garroch
ID: 41871958
Curiosity drives me on :-)

Damn I forgot about that!

I had a quick search and found a few articles on running a presentation from a script using the viewer (EE example and more complex example) but of course we're asking PowerPoint to EDIT the file and the Viewer is a read only thin client, so this approach isn't going to work either so we are at dead end number 2 and I think the final one.
0
 

Author Comment

by:nigelr99
ID: 41872267
I think you're right but I can't fault your effort! In any case I now have a semi-automated way of creating a range of ppt files based on text file content which will still save me 80% of the time. Otherwise, it looks like a losing battle when using the Viewer and it's (understandable) limitations. Thankfully the presentations don't change that often :-)
Many, many thanks again.
1

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PowerPoint is the go-to presentation software for millions of users around the world. Many presentations use basic text features but you can really make special text jump out of your slide by applying this bubble text design process. This article ha…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to edit animations within the presentation, incorporate sound, and set everything up with timing.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question