×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs
Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) > FAQs

VBA Visual Basic for Applications (Microsoft) FAQ

Powerpoint

VBA code to insert Linked Objects in Power Point by SkipVought
faq707-4833
Posted: 10 Feb 04 (Edited 11 Feb 04)

This example adds a new slide and inserts a linked Excel file object into the slide for each file a specified folder.  

Could be a chart or a sheet, but only ONE workbook can be linked since there is no way to specify an object within a file.  

The link displays the chart/sheet that was active when the workbook was saved.

Before you can run this procedure, you must set a reference to the Excel Object Library in the VB Editor via Tools/References.

Option Explicit
Sub Main()
'create a folder and put all the files you want to link
    Dim oFileSystemObject As Object
    Dim oFolderSpec As Object
    Dim oFile As Object
    Dim oFileCollection As Object
    Dim xlApp As Excel.Application
    Dim FolderPath  As Variant
    Dim ppSlide As Slide
    Dim obChart
    
    Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")
    Set xlApp = CreateObject("Excel.Application")
'you must set a reference to the Excel Object Library via Tools/References _
  in order to use GetOpenFilename
    FolderPath = xlApp.GetOpenFilename("Excel Files (*.xls), *.xls")
    If FolderPath <> False Then
        Set oFolderSpec = oFileSystemObject.GetFolder(ReturnPath(FolderPath))
        Set oFileCollection = oFolderSpec.Files
        For Each oFile In oFileCollection
            With ActivePresentation
                Set ppSlide = ActivePresentation.Slides.Add( _
                        Index:=ActivePresentation.Slides.Count + 1, Layout:=ppLayoutBlank)
                Set obChart = ppSlide.Shapes.AddOLEObject( _
                    Left:=120#, Top:=110#, Width:=480#, Height:=320#, _
                    FileName:=ReturnPath(FolderPath) & oFile.Name, _
                    Link:=msoTrue)
            End With
        Next
    End If
    Set xlApp = Nothing
    Set oFileCollection = Nothing
    Set oFolderSpec = Nothing
    Set oFileSystemObject = Nothing
    Set obChart = Nothing
    Set ppSlide = Nothing
End Sub
Function ReturnPath(s)
'this function returns the folder path string in the form _
  "C:\Folders\"
    Dim i As Integer
    For i = Len(s) To 1 Step -1
        If Mid(s, i, 1) = "\" Then Exit For
    Next
    ReturnPath = Left(s, i)
End Function

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close