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
Talk To Other Members