Linking Excel Charts to Powerpoint

[ysommer]

Hi All!
I am trying to write a macro that will export all the charts in a xl file to a new presentation.
The only problem is that I want them to be paste as linked charts. I am doing it now by using "sendkeys", Is there a better way do it?

 

[SkipVought]

ysommer,

Trouble is, a linked object ONLY can be quallified to the FILENAME. Whatever sheet was active when it was last saved is the linked object. Can't have DIFFERENT multi links to one excle file.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ysommer]

Hi Skip,
Maybe I wasn't clear. I want to program a routine that will copy a chart and than instead of pasting it as an image, it would be pasted as a worksheet object in the Powerpoint presentation. Its the same if I copied an excel table
and then in the powerpoint went into edit->paste special-> pastelink -> OK.

Thanks,
Y

 

[SkipVought]

then it would be ONE CHART PER WORKBOOK, OK?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ysommer]

Well it can be one chart per workbook, but I want to run a loop that copies a chart/workbook object in the active workbook and then go over the powerpoint, generate a new slide and paste it as an excel object to the slide.

Thanks,
Y

 

[SkipVought]

here's a sample

Sub Main()
    iSlide = 1
    With ActivePresentation
        .Slides(iSlide).Shapes.AddOLEObject( _
            Left:=120#, Top:=110#, Width:=480#, Height:=320#, _
            FileName:="C:\My Documents\vbaexcel\ChartData.xls", _
            Link:=msoTrue).Select
        iSlide = iSlide + 1
        ActiveWindow.View.GotoSlide _
            Index:=ActivePresentation.Slides.Add( _
                Index:=iSlide, Layout:=ppLayoutBlank).SlideIndex
    End With
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Put all your chart workbooks in one folder. Then try this code

Sub Main()
    Dim fs, f, f1, fc, xlApp As Excel.Application
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set xlApp = CreateObject("Excel.Application")
    folderspec = xlApp.GetOpenFilename("Excel Files (*.xls), *.xls")
    If folderspec <> False Then
        Set f = fs.GetFolder(ReturnPath(folderspec))
        Set fc = f.Files
        For Each f1 In fc
            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(folderspec) & &quot;\&quot; & f1.Name, _
                    Link:=msoTrue)
            End With
        Next
    End If
    Set fs = Nothing
    Set xlApp = Nothing
    Set f = Nothing
    Set fc = Nothing
End Sub
Function ReturnPath(s)
    For i = Len(s) To 1 Step -1
        If Mid(s, i, 1) = &quot;\&quot; Then Exit For
    Next
    ReturnPath = Left(s, i)
End Function

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ysommer]

Works Great...
Tnx alot.


Y