Using Slide object in msaccess

williecg
williecg used Ask the Experts™
on
msaccess, powerpoint, vba

I am using vba in msaccess to change link references in powerpoint. The script I found has

Dim oSld As Slide
Dim oSh As Shape

I am getting an error 429, ActiveX Component can't create object.

I think I do not have the proper object library loaded in msaccess 2016.

Any suggestions.

Thanks,
WillieCg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
Have you selected the Microsoft PowerPoint N.N Object Library in the References library?
Jamie GarrochSenior Technical Consultant at BrightCarbon

Commented:
You can add the PowerPoint X.Y Object Library as Paul suggests and use Early Binding for your non-Access objects. And I would only do this for the development phase because if you intend to distribute the solutions, it may not end up on a PC with the same version of the library and hence will raise a compiler error. It's better to use late bound objects like this:

Dim oSld As Object ' Slide
Dim oSh As Object ' Shape

Open in new window


Even better is to do something like this which allows you to use IntelliSense with the library's OM during dev and then remove the reference and set the DEV_MODE to false for production:

' During Dev : To use IntelliSense, set to true and add a reference to the Microsoft PowerPoint X.Y Object Library
' Production : Set to false and move the reference.
#Const DEV_MODE = True

#If DEV_MODE Then
Dim oSld As Slide
Dim oSh As Shape
#Else
Dim oSld As Object
Dim oSh As Object
#End If

Open in new window


You'll need to do the same for any PowerPoint-specific constants and enumerations.

Author

Commented:
No, I have not. Also I did not see Microsoft PowerPoint N.N Object Library as one of the items in my access 2016 object library.

Suggestions, I am a newbe

thanks
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

Commented:
I do not see the  PowerPoint X.Y Object Library ?

Perhaps there if a reference file I do not have loaded.

thanks,
Jamie GarrochSenior Technical Consultant at BrightCarbon

Commented:
In Access:

1. Press Alt+F11 to open the VBE
2. In the menu, click Tools / References
3. Scroll down until you see Microsoft PowerPoint X.Y Object Library and select it
4. Click OK

Note my comment above regarding early/late bound objects if this solution is to be distributed to multiple users.

Access VBE add Reference to PowerPoint library

Author

Commented:
Thanks,

It loaded with my original install

Microsoft PowerPoint 16.0 Object Library

There is not a reference to Microsoft PowerPoint X.Y Object Library

WillieCg
Paul Cook-GilesSenior Application Developer

Commented:
Willie, is the checkbox for Microsoft PowerPoint 16.0 Object Library checked?...showing selection of PowerPoint reference library...

Author

Commented:
It is checked.
powerpoint 16.0 object library
Paul Cook-GilesSenior Application Developer

Commented:
Huh.  Check the folder (location is defined in the lower part of the window) and confirm that the file is there.   If not, search your "C" drive for the file;  if it is, I'm completely stumped.
Jamie GarrochSenior Technical Consultant at BrightCarbon

Commented:
In order to get a reference to those two objects you need references to their parent objects as in this hierarchy:

PowerPoint Application -> Presentation -> Slide -> Shape

So did you already create references to the application and presentation objects?
John TsioumprisSoftware & Systems Engineer

Commented:
I think it would be most helpful to share a bigger chunk of your code...maybe the error lies some where else...have you created the PowerPoint object ?
e.g.
Dim pwrPoint as Object
Set pwrPoint = CreateObject("Powerpoint.Application")

Open in new window

Author

Commented:
the following works in vb in powerpoint, in access i get "Error 429, ActiveX component can't create object"

here is code

Private Sub cmd_update_OLE_Links_Click()


    Dim oSld As Slide
    Dim oSh As Shape
    Dim sOldPath As String
    Dim sNewPath As String

    ' EDIT THIS TO REFLECT THE PATHS YOU WANT TO CHANGE
    ' Include just the portion of the path you want to change
    ' For example, to change links to reflect that files have moved from
    ' \\boss\p-drive\temp\*.* to
    ' \\boss\Q-drive\temp\*.*
    sOldPath = "\act_34-Bossier"
    sNewPath = "\act_34-North_Bossier"

    On Error GoTo ErrorHandler

    For Each oSld In ActivePresentation.Slides
        For Each oSh In oSld.Shapes
            ' Change only linked OLE objects
            If oSh.Type = msoLinkedOLEObject Then
                On Error Resume Next
                ' Verify that file exists
                If Len(Dir$(Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath))) > 0 Then
                     oSh.LinkFormat.SourceFullName = Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath)
                Else
                      MsgBox ("File is missing; cannot relink to a file that isn't present")
                End If
                On Error GoTo ErrorHandler
             End If
        Next    ' shape
    Next    ' slide

    MsgBox ("Done!")

NormalExit:
    Exit Sub
ErrorHandler:
    MsgBox ("Error " & Err.Number & vbCrLf & Err.Description)
    Resume NormalExit
End Sub
Jamie GarrochSenior Technical Consultant at BrightCarbon

Commented:
Could you pop that code snippet into a code block so we can easily reference the lines? This line will work in PowerPoint because it will be running from the active presentation but in Access, you need to set a reference to the active presentation first:

For Each oSld In ActivePresentation.Slides

Open in new window


Do you plan to have access open the file from the file system or will it already be open or will it need to be created?
Jamie GarrochSenior Technical Consultant at BrightCarbon

Commented:
You need to add this at the top:

Dim oPP as PowerPoint.Application
Dim oPres as Presentation

Open in new window


Then you need to set a reference to the PowerPoint app using either GetObject (PowerPoint is already open) or CreateObject (PowerPoint isn't open):

Set oPP = CreateObject("PowerPoint.Application")

Open in new window


Then you need to open the required presentation file and set a reference to that:

Set oPres = oPP.Presentations.Open("C:\myPresentation.pptx") ' Use a file dialog to get this or is it a fixed folder and file?

Open in new window


Then you access the slide objects as in your original code, now modified to include the above:

Private Sub cmd_update_OLE_Links_Click()

    Dim oPP as PowerPoint.Application
    Dim oPres as Presentation
    Dim oSld As Slide
    Dim oSh As Shape
    Dim sOldPath As String
    Dim sNewPath As String

    ' EDIT THIS TO REFLECT THE PATHS YOU WANT TO CHANGE
    ' Include just the portion of the path you want to change
    ' For example, to change links to reflect that files have moved from
    ' \\boss\p-drive\temp\*.* to
    ' \\boss\Q-drive\temp\*.*
    sOldPath = "\act_34-Bossier"
    sNewPath = "\act_34-North_Bossier"

    On Error GoTo ErrorHandler

    ' Start PowerPoint
    Set oPP = CreateObject("PowerPoint.Application")

    ' Open a presentation
    Set oPres = oPP.Presentations.Open("C:\myPresentation.pptx")

    For Each oSld In oPres.Slides
        For Each oSh In oSld.Shapes
            ' Change only linked OLE objects
            If oSh.Type = msoLinkedOLEObject Then
                On Error Resume Next
                ' Verify that file exists
                If Len(Dir$(Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath))) > 0 Then
                     oSh.LinkFormat.SourceFullName = Replace(oSh.LinkFormat.SourceFullName, sOldPath, sNewPath)
                Else
                      MsgBox ("File is missing; cannot relink to a file that isn't present")
                End If
                On Error GoTo ErrorHandler
             End If
        Next    ' shape
    Next    ' slide

    MsgBox ("Done!")

NormalExit:
    Exit Sub
ErrorHandler:
    MsgBox ("Error " & Err.Number & vbCrLf & Err.Description)
    Resume NormalExit
End Sub

Open in new window

Author

Commented:
I pasted the code into 2016 access vb editor and changed this line

    Set oPres = oPP.Open("y:\test.pptx")

When I ran it I got the following error

compile error: Method or data member not found

Thanks,

WillieCg
Senior Technical Consultant at BrightCarbon
Commented:
Ooops and sorry! That'll teach me not to rush. It's meant to be this:

Set oPres = oPP.Presentations.Open("y:\test.pptx")

Open in new window


It's much easier to write code with the help of IntelliSense than write "blindly" on a mobile device! I've updated it above too in case someone else copies the code.

FYI, IntelliSense is the auto-complete assistant that pops up as you write the line of code. For example, as you write o P P [dot] a list of possible Objects, Methods and Properties are presented dynamically when the [dot] character (or left parenthesis) is typed. In the left example below, we can see that the Open method doesn't exist for the application object oPP but the Presentations object does. So if we choose the Presentations object we then see that there is an Open Method for this object which allows us to open a presentation, adding it to the Presentations collection.

PowerPoint IntelliSense
This will help you debug any issues in the future. If you remove the reference to the PowerPoint library and switch the object variables from Application, Presentation, Slides, Shape etc. (Early Binding) to type Object (Late Binding) then you lose the IntelliSense functionality but you gain cross-platform/version compatibility as it won't matter if the solution runs on Office 2016 or an earlier/later version as the "16.0" library for PowerPoint is not tied to the project and the one installed on the machine will be dynamically assigned at run time as each object reference is set (hence Late Binding).

Author

Commented:
thanks, I am out of town for several days, when I return I will give it a try. thanks again

Author

Commented:
I am back in town, worked great. thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial