Using Slide object in msaccess

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
williecgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Have you selected the Microsoft PowerPoint N.N Object Library in the References library?
Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
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.
williecgAuthor 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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

williecgAuthor 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 BrightCarbonCommented:
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
williecgAuthor 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 DeveloperCommented:
Willie, is the checkbox for Microsoft PowerPoint 16.0 Object Library checked?...showing selection of PowerPoint reference library...
williecgAuthor Commented:
It is checked.
powerpoint 16.0 object library
Paul Cook-GilesSenior Application DeveloperCommented:
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 BrightCarbonCommented:
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 EngineerCommented:
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

williecgAuthor 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 BrightCarbonCommented:
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 BrightCarbonCommented:
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

williecgAuthor 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
Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
williecgAuthor Commented:
thanks, I am out of town for several days, when I return I will give it a try. thanks again
williecgAuthor Commented:
I am back in town, worked great. thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.