Cross application programming 1

[Guest_imported]

I have an Excel file with several charts in it. Let's say the file name is test.xls. I also have an empty PowerPoint file test.ppt that should be created on the test.xls file basis. I need to write the code in Excel file to open PowerPoint file, create a new slide in it and then paste a link from Excel file. The purpose is to automate the presentation creation in case if I have 100 charts in Excel file. Here is the code I wrote:

Sub CreatePresentation()
Dim PowerPointObject As Object
'Create a PowerPoint Object
Set PowerPointObject = CreateObject("PowerPoint.Application&quot
'Open PowerPoint object
PowerPointObject.Visible = True
'Open the file in PowerPoint
PowerPointObject.Presentations.Open FileName:="D:/test.ppt"
ActiveWorkbook.Worksheets("Sheet1&quot.Activate
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

'HERE I need the code to add the slide to the presentation and paste a link from Excel

PowerPointObject.Quit
Set PowerPointObject = Nothing
End Sub

Could anybody help me with this issue? Thank you.

 

[SkipVought]

Hi,

I have never done this before.

But here's what I did.

I created an Excel chart

Copied the chart

Opend a .ppt

started the macro recorder

pasted in .ppt

turned off the macro recorder

viewed the recorded macro - alt+F11

    ActiveWindow.View.Paste
    With ActiveWindow.Selection.ShapeRange
        .Left = 183.75
        .Top = 183#
        .Width = 352.5
        .Height = 174#
    End With

Now here's inserting a new slide

    ActiveWindow.View.GotoSlide Index:=ActivePresentation.Slides.Add(Index:=2, Layout:=ppLayoutBlank).SlideIndex

I am working on a loop to read all the charts on all the sheets.

Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Thanks Skip. The problem is that this code works in PowerPoint VBA, not in Excel. I tried to put it inside my code and immediately got an error: Invalid qualifier (.View is highlighted). Do you have any idea how it can be inserted into Excel - how should I show Excel that when I say 'ActiveWindow' I mean a PowerPoint window? Should it be something like 'PowerPointObject.Activate' (this does not work, just an idea)?
Also, I was thinking maybe my way of coding is wrong. Maybe I should write two independent codes for copy (in Excel) and paste (in PowerPoint) and then create another file that will set the sequence. What do you think? Any ideas about how to create this third file?

Thank you for your help!

 

[SkipVought]

Hi,

In Excel VB editor, Menu Item - Tools/References window, check the Microsoft Powerpoint 9.0 Object library.

The this code will llop thru your file and past charts into ppt

Sub CreatePresentation()
    Dim PowerPointObject As Object, wbk As Workbook, iChart As Integer, iSlideCount As Integer
    'Create a PowerPoint Object
    Set wbk = ActiveWorkbook
    Set PowerPointObject = CreateObject("PowerPoint.Application")
    'Open PowerPoint object
    PowerPointObject.Visible = True
    'Open the file in PowerPoint
    PowerPointObject.Presentations.Open Filename:="D:/test.ppt"
    iChart = 0
    wbk.Activate
    For Each Worksheet In Worksheets
        For Each ChartObject In Worksheet.ChartObjects
            iChart = iChart + 1
            ChartObject.Copy
'            ChartObject.ChartArea.Copy
            PowerPointObject.Activate
            With PowerPointObject
                iSlideCount = .ActivePresentation.Slides.Count
                If iChart > 1 Then
                    iSlideCount = iSlideCount + 1
                    .ActivePresentation.Slides.Add iSlideCount, Layout:=ppLayoutBlank
                End If
                .ActiveWindow.View.GotoSlide Index:=iSlideCount
                With .ActiveWindow
                    .View.Paste
                    With .Selection.ShapeRange
                        .Left = 183.75
                        .Top = 183#
                        .Width = 352.5
                        .Height = 174#
                    End With
                End With
            End With
            wbk.Activate
        Next
    Next
    
    PowerPointObject.Quit
    Set PowerPointObject = Nothing
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Hi! Thanks again for your help. I copied your code and tried to run it. It gives me an error: "View.Paste: Invalid request. Clipboard is empty or contains data which cannot be pasted here." I checked the Excel file while running the code, and yes, it looks like it does not copy data. Am I doing something wrong?
I also tried to redo your code:

Sub CreatePresentation()
On Error GoTo ErrHandler
Dim PowerPointObject As Object, wbk As Workbook, iSlideCount As Integer
Set wbk = ActiveWorkbook
'Get or create a PowerPoint Object
Set PowerPointObject = GetObject(, "PowerPoint.Application&quot
'Open PowerPoint object
PowerPointObject.Visible = True
'Open the file in PowerPoint
PowerPointObject.Presentations.Open FileName:="D:/test.ppt"
iSlideCount = 0
wbk.Activate
Dim WCount As Integer
For WCount = 1 To Worksheets.Count
ActiveWorkbook.Worksheets("Sheet" & WCount).Activate
' I have only one chart on each sheet
ActiveSheet.ChartObjects("Chart 1&quot.Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
PowerPointObject.Activate
With PowerPointObject
iSlideCount = .ActivePresentation.Slides.Count
iSlideCount = iSlideCount + 1
.ActivePresentation.Slides.Add iSlideCount, Layout:=ppLayoutText
.ActiveWindow.View.GotoSlide Index:=iSlideCount
.ActiveWindow.Selection.SlideRange.Shapes("Rectangle 3&quot.Select
.ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
.ActiveWindow.View.Paste
'HERE the .ppt file is closed
End With
wbk.Activate
Next
PowerPointObject.Quit
Set PowerPointObject = Nothing
Exit Sub

ErrHandler:
If Err.Number = 429 Then
Err.Number = 0
Set PowerPointObject = CreateObject("PowerPoint.Application&quot
Resume Next
If Err.Number = 429 Then
MsgBox "MS PowerPoint is not installed on the computer."
End If
End If
End Sub

It pastes data, but for some reason closes the .ppt file immediately after the first loop.

 

[SkipVought]

Your problem is with ppLayoutText.

I am using ppLayoutBlank Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

I tried to run your code, the one with ppLayoutBlank, and it errors on me.
The thing I don't understand is why if I use ppLayoutText the paste command causes the .ppt file to close. Could you try to run my code if you have a chance? Now it's more the curiosity than the necessity, but I need to understand.

Thanks.

 

[SkipVought]

I took you code and ran it and it did not complete.

You are getting an extra slide. I program around that in one of my earlier posts.

I made a few modifications that run...

Sub CreatePresentation()
On Error GoTo ErrHandler
    Dim PowerPointObject As Object, wbk As Workbook, iSlideCount As Integer
Set wbk = ActiveWorkbook
    'Get or create a PowerPoint Object
    Set PowerPointObject = GetObject(, "PowerPoint.Application")
    'Open PowerPoint object
    PowerPointObject.Visible = True
    'Open the file in PowerPoint
    PowerPointObject.Presentations.Open Filename:="D:/test.ppt"
     iSlideCount = 0
     wbk.Activate
    Dim WCount As Integer
    For WCount = 1 To Worksheets.Count
        ActiveWorkbook.Worksheets(WCount).Activate
        ' I have only one chart on each sheet
        ActiveSheet.ChartObjects(1).Activate
        ActiveChart.ChartArea.Select
        ActiveChart.ChartArea.Copy
        PowerPointObject.Activate
            With PowerPointObject
                iSlideCount = .ActivePresentation.Slides.Count
                iSlideCount = iSlideCount + 1
                .ActivePresentation.Slides.Add iSlideCount, Layout:=ppLayoutText
                .ActiveWindow.View.GotoSlide Index:=iSlideCount
                .ActiveWindow.Selection.SlideRange.Shapes("Rectangle 3").Select
                .ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
                .ActiveWindow.View.Paste
                'HERE the .ppt file is closed
                End With
            wbk.Activate
     Next
   PowerPointObject.Quit
   Set PowerPointObject = Nothing
Exit Sub

ErrHandler:
    If Err.Number = 429 Then
        Err.Number = 0
        Set PowerPointObject = CreateObject("PowerPoint.Application")
        Resume Next
        If Err.Number = 429 Then
            MsgBox "MS PowerPoint is not installed on the computer."
        End If
    End If
End Sub

Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Thank you, thank you!!!
And if I get rid of the following two lines: PowerPointObject.Quit
Set PowerPointObject = Nothing, the presentation is complete and stays up.

Actually... my code is working too )). Thanks again for your help!!!

 

[SkipVought]

May I strongly suggest that you register as a Tek-tips user. You will benefit from this forum and others and may also become a contributor as well. Skip,
metzgsk@voughtaircraft.com

 

[Guest_imported]

Thank you for suggestion. I am not a VBA programmer (this code was my second experience), I work with SQL Server and ASP, so I won't use this particular forum much. On the other hand if this registration is universal for all forums on this site (and I guess it is), then I will probably become a member.

 

[SkipVought]

It is --

So, whatever Forum(s) fit your fancy, have at 'em! Skip,
metzgsk@voughtaircraft.com

 

[Ankor]

Done

 

[nikkindc]

Is there a way to copy the Excel data to Powerpoint and maintain the links via VBA? Manually, selecting the print area, copying it & going to Powerpoint & selecting Edit - PasteSpecial - Link - Excel worksheet works. Anyway to do this programatically using VBA? Thanks.