Access, SQL, VBA & PowerPoint.

[NelsonFerro]

Hello.

So I have an Access application in Access 2007, that connects to a SQL Server to retrive data, with only forms, all the connection to SQL is made by VBA.

With this application the user is able to edit is perfil and with that generate a word file (done using bookmarks) or a powerpoint file.

Now this is where things get tricky, powerpoint does not suport bookmarks. And all the examples i find talk about creating a new powerpoint presentation, but my goal is to fill a powerpoint template with dynamic data, because i also have static data.

I don't need any kind of anymations and it only has one slide.

Any sugesttions or examples, will be highly appreciated, thank you all for your time.

 

[Rx_]

Please provide a little more specific informaiton.
Do you want to automate creating a PPT slide with your Access data (from sql server).
Do you also want users to fill in data and then retreive it; like some kind of user form?
Correct, there are no bookmarks or tags that would make that a snap.

Will just take a stab at this based on some assumptions.
A template is created or VBA automation is used to create a template with some fields filled in, and other to be filled in by user.
The code below would retreive all text from all objects (with out a bookmark or tab)
So long as the user didn't change (delete) a textbox, it should all be returned in the same order.
A Text box does have a Visible property. So, use a text box in Visible = False as your bookmark in front of the visible text box the users will fill in.
It would appear that if you created this invisible textbox before the user visible text box, the ordinal order will be harvested. It is not a real bookmark, but it provides some type of reference. By using a text box for both, you can harvest just the text boxes and ignore all the other shape types.
Example of PPT harvest code.

[COLOR=#00008b]Sub[/COLOR] GetAllText()
[COLOR=#00008b]Dim[/COLOR] p [COLOR=#00008b]As[/COLOR] Presentation: [COLOR=#00008b]Set[/COLOR] p = ActivePresentation
[COLOR=#00008b]Dim[/COLOR] s [COLOR=#00008b]As[/COLOR] Slide
[COLOR=#00008b]Dim[/COLOR] sh [COLOR=#00008b]As[/COLOR] Shape
[COLOR=#00008b]For[/COLOR] [COLOR=#00008b]Each[/COLOR] s [COLOR=#00008b]In[/COLOR] p.Slides
    [COLOR=#00008b]For[/COLOR] [COLOR=#00008b]Each[/COLOR] sh [COLOR=#00008b]In[/COLOR] s.Shapes
        [COLOR=#00008b]If[/COLOR] sh.HasTextFrame [COLOR=#00008b]Then[/COLOR]
            [COLOR=#00008b]If[/COLOR] sh.TextFrame.HasText [COLOR=#00008b]Then[/COLOR]
                Debug.Print sh.TextFrame.TextRange.Text
            [COLOR=#00008b]End[/COLOR] [COLOR=#00008b]If[/COLOR]
        [COLOR=#00008b]End[/COLOR] [COLOR=#00008b]If[/COLOR]
    [COLOR=#00008b]Next[/COLOR]
[COLOR=#00008b]Next[/COLOR]
[COLOR=#00008b]End[/COLOR] [COLOR=#00008b]Sub[/COLOR]

You were not clear if this is something the user opens, changes (edit) and closes (saves as). Then the code above would be an example of how to retrieve the edited values.

If you were asking to create a Power Point slide with active text box - the user fills in the text box during the presentation - this would be an idea on how to grab the text being changed.

Option Explicit
' live captuer of Powerpoint text box change at runtime
Dim TexBoxTestChanged As Boolean
Private Sub TextBoxTEST_Change()
   TexBoxTestChanged = True
End Sub
Private Sub TextBoxTEST_LostFocus()
  If TexBoxTestChanged Then
    MsgBox "TextboxTEST changed value to: " & Me.TextBoxTEST.Value, vbOKOnly, "Changed Text Event"
    ' take the Me.TextBoxTEST.Value - put it into a variable
    ' There is no Slide Close event - so Write this value to your outside data store immediately
  End If
 TexBoxTestChanged = False ' resets the change
End Sub

Basically during the PPT session, the changes would be written to something outside PowerPoint at each lost focus for each text box.

This is not intended to be a working solution, just a suggestion on how to approach the problem. Good Luck!

 

[NelsonFerro]

Rx_ first of all, thank you in the quick answer.

The ppt should be open thourght a button present in the Access form.

I don't want to create a new ppt, i have the file, with a lot of static information, and i just want to open the file and add the dynamic information, like the user name, email, phone, etc..

All the data comes from the sql, but i'm showing the data to the user in a Access Form they can modify it there, no needed to do it in the ppt, and is the data that's present in the Form that is send to the ppt (at least that's what i'm doing with the word file), but i can also make a new Query to the SQL, makes me no diference.

So the main ideia is that when the ppt is created the user just have to email it, or print it, or save it, but that's the users choice, no automation needed here, so no edits in the ppt.

I'll try your code see if it fits my needed, once again thank you very much

 

[NelsonFerro]

OK, Rx_ based in your code and some more google. i'm half way.

This is my current code, warning it's very raw code, don't be chocked

Dim pptobj As PowerPoint.Application
Dim Presentation As PowerPoint.Presentation
Set pptobj = New PowerPoint.Application
 
Set pptobj = CreateObject("Powerpoint.Application")
pptobj.Activate
Set Presentation = pptobj.Presentations.Open("C:\bababababa\aaa.pptx")
pptobj.Visible = True
pptobj.WindowState = ppWindowMaximized
 
pptobj.ActivePresentation.Slides(1).Shapes("name").TextFrame.TextRange.Text = (CStr(Forms!CVShort!txtname))
pptobj.ActivePresentation.Slides(1).Shapes("category").TextFrame.TextRange.Text = (CStr(Forms!CVShort!TxtCategory))

etc....

Now, my problem is i have to send a image path, and add a new silde everytime the information is to big to just one silde, any ideias?

 

[Rx_]

Just a wild suggestion
Haven't tried this due to schedule so watch for typo.
Please post your working code for others to enjoy.

PowerPoint Visual Basic Editor [Alt]+[F11].
Insert - Module
Thr this

 Sub InsertLogo()
  'Insert a graphic logo.
  ActiveWindow.Selection.SlideRange.Shapes.AddPicture( _
   FileName:="C:\Rx\Received Nobel Prize for Snappy Code\Rx.jpg", _
   LinkToFile:=msoFalse, _
   SaveWithDocument:=msoTrue, Left:=60, Top:=35, _
   Width:=98, Height:=48).Select
End Sub

The image is placed relative to Left and Top values of the slide.

Subroutine Testing: select a slide and then Tools - macro.
Select InsertLogo, from the list and Run.

My preference it to pass a file name in as a parameter. My preference is to use a Boolean to show Pass Fail.

Function InsertLogo(gfilename As String) as Boolean
  'Insert Rx logo. 

  ActiveWindow.Selection.SlideRange.Shapes.AddPicture( _
   FileName:=gfilename, _
   LinkToFile:=msoFalse, _
   SaveWithDocument:=msoTrue, Left:=60, Top:=35, _
   Width:=98, Height:=48).Select
If Err.Number = 0 then 
    InsertLogo = True
Else
    InsertLogo = False
End If

End Function

The Parameter, gfilename, is the entire pathname if not in the same folder as the Power Point presentation file.

I forgot to add parameters for the Top and Left - or the photos will just stack. Sorry, no time left to respond.