Excel to Powerpoint VBA?

[cdhoughton]

Hi all

I have a Powerpoint presentation with a number of linked OLE tables that feed through from Excel data tables.

I want to use VBA to do the following:

1. Create a new copy of the *.PPT with a specified name
2. Use VBA to update the links in the *.PPT to that of the source workbook (at present the PPT links to a master spreadsheet but when this is copied, I want the copy PPT to update the links to the copy XLS)

Does anyone know if this is possible and if so a starting point for me to do this?

Cheers in advance

Chris

 

[cdhoughton]

Hi all

I have now solved this problem!

Cheers

Chris

 

[abiram01]

Hi,

Can please you provide the solution..?

 

[cdhoughton]

Basically you need the following variables:

LinkPath (string) - set this to the path of the Excel sheet you want to link the PPT to

OLE1 - OLEx - set these to the cell ranges in the sheets you are linking to Powerpoint e.g.

OLE1 = "!Front!R1C1"
OLE2 = "!Front!R3C1"
OLE3 = "!P&L!R3C2:R20C6"
OLE4 = "!BS!R3C2:R22C6"
OLE5 = "!Front!R2C1"
OLE6 = "!CF!R4C2:R16C5"

You then need to get the Id of each OLE object in Powerpoint - the easiest way to do this is to run a loop using Powerpoint VBA to display a message box that runs in order of all the OLE objects.

Then in Excel VBA, adapt the following code:

'Open PPT
Set PPA = CreateObject("Powerpoint.Application")
PPA.Visible = True
PPA.Presentations.Open Dest 'Set Dest to path of PPT

For Each sld In PPA.ActivePresentation.Slides
    For Each sh In sld.Shapes
        
        If sh.Type = msoLinkedOLEObject Then
            If sh.OLEFormat.progID = "Excel.Sheet.8" Then
                
                Select Case sh.ID
                Case 28678
                    sh.LinkFormat.SourceFullName = LinkPath & OLE1
                Case 28680
                    sh.LinkFormat.SourceFullName = LinkPath & OLE2
                Case 28681
                    sh.LinkFormat.SourceFullName = LinkPath & OLE3
                Case 36951
                    sh.LinkFormat.SourceFullName = LinkPath & OLE4
                Case 43125
                    sh.LinkFormat.SourceFullName = LinkPath & OLE5
                Case 49272
                    sh.LinkFormat.SourceFullName = LinkPath & OLE6
                End Select
                
            Else
                
            End If
        End If
    Next
Next