Excel to Powerpoint

[Keroro6666]

Hi there

Please assist me with the following automating task using Excel 2010...

I need to write a macro, from my current workbook that contains the data, that will:
- copy the data from my current sheet
- open powerpoint
- use PowerPoint's Insert chart function as seen on the ribbon (choose bar graph) and upon which an excel sheet will appear to contain data for the particular chart
- paste the data from current worksheet into the sheet that was opened by powerpoint as values (once the data has been pasted, the chart should have formed it's shape)
- and return back to my excel workbook.

Ideally, at the end of the day, this automation should go thru each of my worksheets to get data and insert charts via PowerPoint which should just the loop through the above tasks.

However I am already having trouble without the looping and would like to know for now how to tell PowerPoint to implement it's insert chart function and paste my data using VBA .


I know that a potential solution is to make graphs inside excel and paste them into PowerPoint, but that is precisely what I am trying to avoid because I don't want to depend too much on the linking of data between PowerPoint and Excel.

Please please help!

Thanks

 

[nicks43]

Dear Keroro.

the task you are describing is kind of interesting since typically there are charts copied over from Excel to Powerpoint. But anyway, this is something which I will think of in future as well.
But for now: In order to create a chart you can use something like this:

Activepresentation.Slides(1).Shapes.AddChart2 -1, xlColumnClustered, 50, 50, 400, 200

And then access the data for this chart using something like:

Activepresentation.slides(1).Shapes(Activepresentation.slides(1).shapes.count).chart.chartdata.workbookworksheets(1).usedrange.value = yourDataRange.value

Of course, this kind of idea needs to be embedded and adapted within your Excel VBA code. Then this should basically work.

Another option, if you just want to avoid linking, then you could also copy over as image, either using VBA (there are plenty of examples in the web) or using some out of the box Excel to Powerpoint automation software such as SlideFab. Then you would not need to implement it yourself.

Bye.
Nicks

 

[Keroro6666]

Hi there,

Thanks for the assist.

Do you know how to incorporate that into my code?

Sub Test()    Dim RangeName As String
    Dim newPowerPoint As PowerPoint.Application
    Dim activeSlide As PowerPoint.slide
    Dim cht As Excel.ChartObject
    Dim myChart As PowerPoint.Chart
    
    Worksheets(2).Select
    RangeName = "M5:P58"
    
    Range(RangeName).Select
    Selection.Copy
    
    On Error Resume Next
        Set newPowerPoint = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
    
    'Let's create a new PowerPoint
    If newPowerPoint Is Nothing Then
        Set newPowerPoint = New PowerPoint.Application
    End If
'Make a presentation in PowerPoint
    If newPowerPoint.Presentations.Count = 0 Then
        newPowerPoint.Presentations.Add
    End If
    
    'Set myChart := ActivePresentation.Slides(1).Shapes.AddChart(Type:=xlColumnClustered).Chart
    'Error: syntax error

         'ActivePresentation.Slides(1).Shapes.AddChart2 -1, xlColumnClustered, 50, 50, 400, 200
'Error: method or data member not found






    
    


End Sub

I get a syntax error when I try run the line with the ActivePresentation. etc...
I gave the description of the errors below each alteration.

Also, intellisense doesn't give me the addchart2 option when it automatically displays my options.

 

[DushiPunda]

Do you have the Microsoft PowerPoint Object Library reference added? Open up VBE (ALT+F11), go to Tools > References...scroll for the next 9 years until you reach "Microsoft PowerPoint 14.0 Object Library" and check it. The code should work after that.

 

[Keroro6666]

Hi

Yes, I did.
In particular, which line of the ActivePresent.etc should work?

 

[DushiPunda]

Sorry, I guess I should have clarified...provided it's coded correctly, you're using the correct syntax and all of that, and that you have that reference added, it should work.

 

[DushiPunda]

Hi

Yes, I did.
In particular, which line of the ActivePresent.etc should work?

So I think you've got some things wrong. Here's a macro I'm using in one of my workbooks:

Sub CopyToPPT()
Dim ppApp As Object 'PowerPoint.Application
Dim ppPres As Object 'PowerPoint.Presentation
Dim ppSlide As Object 'PowerPoint.Slide
Dim ppShape As Object 'PowerPoint.Shape
Dim destPPT as String, destPPTName as String

destPPT = "C:\PresentationName.ppt"
destPPTName = "PresentationName.ppt"

On Error Resume Next

Set ppApp = GetObject(, "PowerPoint.Application")

On Error GoTo 0

Worksheets("Sheet1").Activate
Set Rng = Worksheets("Sheet1").Range("A1:L23")
Application.CutCopyMode = False
Rng.Copy

If ppApp Is Nothing Then Set ppApp = CreateObject("PowerPoint.Application")

If ppApp.Presentations.Count > 0 Then
    For Each CurrentlyOpenPresentation In ppApp.Presentations
        If CurrentlyOpenPresentation.Name = destPPTName Then IsOpen = True

        If IsOpen = True Then
            On Error Resume Next
            ppApp.ActivePresentation.SlideShowWindow.View.Exit
            On Error GoTo 0
            ppApp.ActiveWindow.View.GotoSlide ppApp.Presentations(destPPT).Slides.Count
        End If

        If IsOpen = True Then GoTo ProgressWithScript
    Next CurrentlyOpenPresentation
End If

Is Open = False

If IsOpen = False Then ppApp.Presentations.Open Filename:=destPPT

ProgressWithScript:
Set ppPres = ppApp.ActivePresentation

ppApp.Visible = 1

For Each ppSlide In ppPres.Slides
    For Each ppShape in ppSlide.Shapes
        If ppShape.Type = msoPicture Then
            ppShape.Delete
        End If
    Next ppShape
Next ppSlide

Set ppSlide = ppPres.Slides(1)
ppPres.Slides(1).Shapes.PasteSpecial DataType:=2
Set ppShape = ppPres.Slides(1).Shapes(ppSlide.Shapes.Count)

ppShape.Left = 0
ppShape.Top = 0
ppShape.Height = 502
ppShape.Width = 720

ppApp.Activate
ppApp.Save
ppPres.SlideShowSettings.Run
End Sub

So, I'm copying a range in excel, testing if powerpoint is open, if it's not then I open it. Then I test to see if I already have the correct presentation open, if I don't, then I open it. Then I delete the picture on slide 1 (I pretty much needlessly loop through all of the slides and objects...both of which there are only one). Then I paste what I copied from excel as a picture and edit the properties of my new picture. Then I set it to slide show mode.

Obviously this isn't the exact thing you're trying to do...but it should, at least, get you off on the right foot.