MS Access and MS PowerPoint Textboxes and Image Control

[SillyMonkeyPro]

Hi All


I hope you can help me out.

I'm new to development and develop in MS Access.

I have come across some work which i'm abit lost with.

I am trying to get 3 textboxes and a picture to be placed in MS PowerPoint so it can run a slide show but it needs to come from MS Access


My controls on my form are

txtTitle
txtRefNumber
txtAboutMe
imgPicture


I got txtTitle and txtRefNumber in the same line (I got that to work with my MS PowerPoint).
I am trying to get the layout to be

txtTitle and txtRefNumber

imgPicture

txtAboutMe

Okay so far this is what I have :

Code: Select all

 Dim objPresentation As PowerPoint.Presentation
    Dim objSlide As PowerPoint.Slide
    Dim strFileName As String
    
   
    
    'Ensure that both the title and the picture are selected
    If IsNull(Me.txtTitle) Or IsNull(Me.txtRefNumber) Or Me.imgPicture.Name = "" Then
    
    
        MsgBox "A Title And A Reference Number Must Be Entered , and a Picture Selected Before Proceeding", vbRetryCancel, "Make PowerPoint Slides"
        
       
    Else
        
        'Create instance of PowerPoint application
        Set mobjPPT = New PowerPoint.Application
        
        'Make instance visible to user
        mobjPPT.Visible = True
        
        'Add a Presentation
        Set objPresentation = mobjPPT.Presentations.Add
        'Add a Slide
        
        Set objSlide = objPresentation.Slides.Add(1, ppLayoutClipartAndText)
        
        'Change the Slide Background
        objSlide.Background.Fill.ForeColor.RGB = RGB(189, 130, 255)
        
        
        
        
        
        'Modify the Slide Title
        With objSlide.Shapes.Title.TextFrame.TextRange
            .Text = (Me.txtTitle) & "-" & Me.txtRefNumber & "                " & Me.txtAboutMe
            .Font.Size = 74
            .Font.Color.RGB = RGB(255, 100, 255)
            .Font.Italic = True
            .Font.Bold = msoTrue
        End With
        
        
        'Add the picture to the slide
        strFileName = imgPicture.Picture
        objSlide.Shapes.AddPicture FileName:=strFileName, _
        Left:=1000, Top:=2550, _
        LinkToFile:=msoFalse, SaveWithDocument:=msoTrue


    End If
    
cmdMakePPTSlide_Exit:
    Set objPresentation = Nothing
    Set objSlide = Nothing
    Exit Sub
    
cmdMakePPTSlide_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume cmdMakePPTSlide_Exit
End Sub

Hope you can help

[HansV]

Welcome to Eileen's Lounge!

PowerPoint VBA is probably the weirdest of all Office VBA dialects, but here is an attempt:

Code: Select all

Private Sub cmdCreateSlide_Click()
    Dim mobjPPT As PowerPoint.Application
    Dim objPresentation As PowerPoint.Presentation
    Dim objSlide As PowerPoint.Slide
    Dim strFileName As String
    Dim shp0 As PowerPoint.Shape
    Dim shp1 As PowerPoint.Shape
    Dim shp2 As PowerPoint.Shape

    'Ensure that both the title and the picture are selected
    If IsNull(Me.txtTitle) Or IsNull(Me.txtRefNumber) Or Me.imgPicture.Picture = "" Then
        MsgBox "A Title And A Reference Number Must Be Entered, " & _
            "and a Picture Selected Before Proceeding", vbExclamation, "Make PowerPoint Slides"
    Else
        'Create instance of PowerPoint application
        Set mobjPPT = New PowerPoint.Application

        'Make instance visible to user
        mobjPPT.Visible = True

        'Add a Presentation
        Set objPresentation = mobjPPT.Presentations.Add
        'Add a Slide

        Set objSlide = objPresentation.Slides.Add(1, ppLayoutClipartAndText)

        'Change the Slide Background
        objSlide.Background.Fill.ForeColor.RGB = RGB(189, 130, 255)

        'Modify the Slide Title
        Set shp0 = objSlide.Shapes.Title
        With shp0.TextFrame.TextRange
            .Text = Me.txtTitle & "-" & Me.txtRefNumber
            .Font.Size = 74
            .Font.Color.RGB = RGB(255, 100, 255)
            .Font.Italic = True
            .Font.Bold = msoTrue
            .ParagraphFormat.Alignment = ppAlignCenter
        End With

        'Add the picture to the slide
        strFileName = imgPicture.Picture
        If InStr(strFileName, "\") = 0 Then
            strFileName = CurrentProject.Path & "\" & strFileName
        End If
        Set shp1 = objSlide.Shapes.AddPicture(Filename:=strFileName, _
            Left:=shp0.Left, Top:=shp0.Top + shp0.Height, _
            LinkToFile:=msoFalse, SaveWithDocument:=msoTrue)
        shp1.Left = shp0.Left + (shp0.Width - shp1.Width) / 2
        Set shp2 = objSlide.Shapes.Placeholders(3)
        With shp2.TextFrame.TextRange
            .Text = Me.txtAboutMe
            .Font.Size = 74
            .Font.Color.RGB = RGB(255, 100, 255)
            .Font.Italic = True
            .Font.Bold = msoTrue
            .ParagraphFormat.Alignment = ppAlignCenter
            .ParagraphFormat.Bullet = msoFalse
        End With
        With shp2
            .Left = shp0.Left
            .Width = shp0.Width
            .Top = shp1.Top + shp1.Height
        End With
    End If

cmdMakePPTSlide_Exit:
    Set objPresentation = Nothing
    Set objSlide = Nothing
    Exit Sub

cmdMakePPTSlide_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description, vbExclamation
    Resume cmdMakePPTSlide_Exit
End Sub

[SillyMonkeyPro]

Hi Thank You for the welcome and the attempt of answering my question

I am however still having the same problem.

I can not get the txtAboutMe and the Image into MS PowerPoint.

I will send you some attachments- Please let me know if you can help.

Many Thanks again and I hope to hear from you.

[HansV]

Do you get an error message?

(I won't be able to reply for the next hour or two)

[HansV]

I have attached a zip file with:

1) A sample database with a single form.

S0169.png

2) The picture file used for the image.
3) The PowerPoint presentation produced by the code.

S0170.png

I have modified the code:
- It now creates a slide with a title placeholder only; the picture and text box are created from scratch.
- The code uses late binding, so that it doesn't need a reference to the Microsoft Office and Microsoft PowerPoint object libraries.

PPT_Test.zip

[SillyMonkeyPro]

Hi HansV

Thank You so so so much.

This seems to be exactly what I asked for, There is one more part to what I was looking for in total but let me look at the code and try come up with the answer after that if I am unable to come up with it I will revert to you

Im up bright and early to look at the replies. You made my day

[SillyMonkeyPro]

Hi

I'm back once again - I have tired to implement what I was doing but to little avail.

from the last post.

I have made the form a continues form (so many more pictures can be attached) I Have also added a table to the database hope to be a temp table later on, and I have split the 1 form into 2 different forms.

I need to have all the pictures being put into a single PowerPoint presentation just as different slides.

so each picture would have its own

txtTitle-txtRefNumber
imgPicture
txtAboutMe

After I have made these changes I have an error msg (I will attach it)

I hope you are able to help me yet again




I really want to become good in table design and Access vba if you know of an online site I can use to get this skills please let me know.

[HansV]

The code expects that the Picture property of the image control contains a valid file name, and that the file specified by this file name is available. We cannot copy an image directly from Access to PowerPoint; it has to be done via a file on disk.

[SillyMonkeyPro]

Hi

Yes, I have seen that and it does use code to get a path which I use

I can not seem to understand what is going wrong.

[HansV]

Above the line

Code: Select all

        Set shp1 = ...

insert the following line:

Code: Select all

        MsgBox strFileName

You'll see the value of strFileName. Does it contain the path and filename of an existing file?

[SillyMonkeyPro]

Hi

I get a path back as i have linked it to the table I will upload that database now as well.

I see the (none) at the end of the path but still am lost as I have got it to see the path from the table field, so it should be clear

[HansV]

Do you have a field in the table that contains the path and file name? If so, what is the name of that field?

[SillyMonkeyPro]

Hi

Yes, I do have a field on a table called Picture_Path.

[HansV]

Thanks. Replace the lines

Code: Select all

        strFileName = imgPicture.Picture
        If InStr(strFileName, "\") = 0 Then
            strFileName = CurrentProject.Path & "\" & strFileName
        End If

with

Code: Select all

        strFileName = Me.Picture_Path

[SillyMonkeyPro]

Hi

Thanks that works well, magic real magic

But there is just 1 final aspect I am trying to see to.

How would I get all the pictures into MS PowerPoint by clicking the button to create slide once ?

Many Thanks again for all your help again, it really means a lot

[HansV]

What do you mean by "all the pictures"? Do you want to loop through all records in the table?

[SillyMonkeyPro]

Hi

Yes like a loop.

But the data is on a table which currently populates the form and the button on the form (Create Slide),it places the picture and its data in to new MS PowerPoint Presentation,

Is there a way to get the pictures and the data into 1 MS PowerPoint Presentation? (Maybe with 50 slides depending on amount of records on the table)

[HansV]

Here is a version that loops through the records of tblTempTable:

Code: Select all

Private Sub cmdCreateSlide_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim mobjPPT As Object
    Dim objPresentation As Object
    Dim objSlide As Object
    Dim strFileName As String
    Dim shp0 As Object
    Dim shp1 As Object
    Dim shp2 As Object

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblTempTable", dbOpenForwardOnly)

    'Create instance of PowerPoint application
    Set mobjPPT = CreateObject(Class:="PowerPoint.Application")

    'Make instance visible to user
    mobjPPT.Visible = True

    'Add a Presentation
    Set objPresentation = mobjPPT.Presentations.Add

    Do While Not rst.EOF
        ' Add a slide
        Set objSlide = objPresentation.Slides.Add(1, 11) ' 11 = ppLayoutTitleOnly

        'Change the Slide Background
        objSlide.Background.Fill.ForeColor.RGB = RGB(189, 130, 255)

        'Modify the Slide Title
        Set shp0 = objSlide.Shapes.Title
        With shp0.TextFrame.TextRange
            .Text = rst!Title & "-" & rst!Reference_Number
            .Font.Size = 74
            .Font.Color.RGB = RGB(255, 100, 255)
            .Font.Italic = True
            .Font.Bold = True
            .ParagraphFormat.Alignment = 2 ' ppAlignCenter
        End With

        'Add the picture to the slide
        strFileName = rst!Picture_Path
        Set shp1 = objSlide.Shapes.AddPicture _
            (FileName:=strFileName, _
            Left:=shp0.Left, Top:=shp0.Top + shp0.Height, _
            LinkToFile:=False, SaveWithDocument:=True)
        shp1.Left = shp0.Left + (shp0.Width - shp1.Width) / 2
        ' 1 = msoTextOrientationHorizontal

        ' Add the About Me text box
        Set shp2 = objSlide.Shapes.AddTextbox _
            (Orientation:=1, _
            Left:=shp0.Left, Top:=shp1.Top + shp1.Height, _
            Width:=shp0.Width, Height:=36)
        With shp2.TextFrame.TextRange
            .Text = rst!About_Me
            .Font.Size = 24
            .Font.Color.RGB = RGB(255, 100, 255)
            .Font.Italic = False
            .Font.Bold = True
            .ParagraphFormat.Alignment = 2 ' ppAlignCenter
            .ParagraphFormat.Bullet = False
        End With

        ' On to the next record
        rst.MoveNext
    Loop

cmdMakePPTSlide_Exit:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Set objPresentation = Nothing
    Set objSlide = Nothing
    Exit Sub

cmdMakePPTSlide_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description, vbExclamation
    Resume cmdMakePPTSlide_Exit
End Sub

[SillyMonkeyPro]

Hi

I have tried out the code, I do get an error when running it.
I have attached a picture of the error so you are able to view it.
The error is on " Dim dbs As DAO.Database",if i comment that piece of code then the error gos to "Dim rst As DAO.Recordset"

I hope to hear from you soon.

[Rudi]

Hi,

Check to see if you have a reference to the Microsoft DAO library.
To access the option, go to the macro and in the VBE menu, click, Tools and then References
Scroll down the list in the dialog and select Microsoft DAO 3.x Object Library
Choose OK

Try running the code again...

1.jpg