New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

VBA resizing a cell range and chart linked from Excel in PPT

Macros, VBA, Excel Automation, etc.

VBA resizing a cell range and chart linked from Excel in PPT

Postby ez08mba » Fri Feb 10, 2012 11:56 am

All my VBA is opening, formatting, saving and closing PPT from Excel. I'm new to VBA and have been try to resize cell ranges and charts Pasted Special and linked. I've seen a number of examples but haven't been able to get them working with my current code. (from my phone because my internet is down - so there are no equal signs or brackets available in the code I typed below)

Code: Select all
PPPres.Slides(3).Select
Range("A1:D20").Copy
PPPres.Slides(3).Shapes.PasteSpecial DataType: ppPasteOLEObject, Link: msoTrue

Set PPSlide   PPPres.Slides(3)
PPSlide.Shapes(1).Select
With Selection
.Height 200
.Width 500
End With

Remember that I don't have an "equals" on my phone so I know they are missing.

I don't know how to place this code? I don't know how to write for two ranges and one chart on the next slide.
  • 0

ez08mba
Regular
 
Posts: 68
Joined: Feb 2, 2012
Location: Dayton, Ohio
Reputation: 0
Excel Version: 2007/2010

Re: VBA resizing a cell range and chart linked from Excel in PPT

Postby Sisyphus » Fri Feb 10, 2012 11:51 pm

hello ez08mba,
I'm afraid there is very little I can do to help you. :cry:
Basically, in VBA all code is structured into modules and procedures. A module is a code sheet. When you first open a new workbook's code in the VB editor there are a number of code sheets already installed: One for each worksheet and one for ThisWorkbook. All of these are kind of special. You should create a new sheet. Rightclick on the project in the Project Explorer window, select Insert and 'Module'. You get a new Module called 'Module1' by default. That is where you put your procedures.
Most procedures you will need are either functions or subs (sub-routines). You will be well advised to start their declaration with either the word Private or Public. They should be Public if you intend to call them from anywhere outside the code module itself, including other code modules or the keyboard. The next word is either Sub or Function. Your present code is a Sub. A function would return a value or something to the calling procedure. Then you need a name. Call it 'TryMe'. Any name is fine but avoid special characters such as you would not be allowed in file names. The name can't consist of more than one word. Use underscrores if you must, but they are out of fashion now. The current sense of beauty is to capitalize each new word. Now you are almost done. Not quite because you must end the declaration with a pair of brackets. For now, they should be empty. So, your line will look like this:
Code: Select all
Public Sub TryMe()
    ' put your code here
End Sub

End Sub (or End Function) is a requirement. You must tell the compiler where the code ends.
All your code goes between these two lines. And that is when your problems really start in earnest:

VBA defines objects which have methods and properties. The classic schoolroom example is that of a radio that has buttons to select stations or adjust volume. The radio is the object, the buttons are the methods. Then, the volume turned up or down and the station currently selected, that are the properties. Now, obviously, there is no point in telling me to turn down the volume of my radio unless I have a radio, and there is no point in telling me to get a radio unless I know what a radio is.

The Excel object library knows all objects required by Excel and the PowerPoint object library knows all objects required by PP. Excel doesn't know what a 'Slide' is. Excel has an ActiveSheet, PP knows its ActivePresentation etc. By default, when you start the VBE in Excel that application's object library will be loaded and an instance of the Excel application will be started. From the VBE window you can select Tools/References and put a check next to the Microsoft PowerPoint object library. That would add that library, and Excel would even remember to load it again next time you start Excel. But it wouldn't give you an instance of the PowerPoint application. That you would need to create with a line of code like this
Code: Select all
Set PPapp = GetObject(, "PowerPoint.Application")

Now, if you talk to VBA about Slides and Presentations you will be understood. But you need to be careful. There are some objects that are common to both but slightly different. I am not familiar with VBA for PowerPoint but, let us presume that the Range object exists in both libraries you can keep them apart by calling one an Excel.Range and the other a PowerPoint.Range. Normally, you do this at the top of your procedure where you dimension (define) your variable names.

In your code you have two objects, PPPres and PPSlide. You should declare them like this:
Code: Select all
Public Sub TryMe()
    Dim PPapp as PowerPoint.Application
    Dim PPPres As Presentation
    Dim PPSlide as Slide

    Set PPapp = GetObject(, "PowerPoint.Application")
    ' your remaining code follows here
End sub

Now your code starts with the broadside: "PPPres.Slides(3).Select"
You have told VBA that PPPres is a presentation. You haven't specified which one. Try
Code: Select all
    Set PPPres = ActivePresentation
    PPPres.Slides(3).Select

With your next line of code you jump to the Excel application object: "Range("A1:D20").Copy".
If there is a Range object in Powerpoint this line will cause confusion. You also haven't specified which workbook or which worksheet. Please have some pity on VBA and Excel, both. How can they follow your thoughts? Try something like this:
Code: Select all
    Dim Ws as Excel.Worksheet
    Set Ws = ActiveSheet
    Ws.Range("A1:D20").copy

Remember to put the Dim statement at the top of the procedure, together with the others.

Your next command is confusing if viewed in context: "PPPres.Slides(3).Shapes.PasteSpecial "
You have defined PPPres as the ActivePresentation. Hopefully, it has at least 3 slides. So you should be able to paste something there, right? I agree. But I wonder why you selected it. You don't need to, unless you wanted to say
"Selection.Shapes.PasteSpecial "
I don't like the Selection object. Therefore I think your first line is superfluous.

Now you define the other object: "Set PPSlide = PPPres.Slides(3)". Had you done this earlier, say instead of selecting that slide, you could have used the object with "PPPSlide.Shapes.PasteSpecial". Anyway, better late than never. :D
Now you use this object in "PPSlide.Shapes(1).Select". Why do you want to select it?
:idea: To create a selection object! Then you want to manipulate the selection object.
The Selection object you created from the PPSlide object is identical. Both objects are fully interchangable. You tell VBA "make a copy of PPSlide, then make changes to the copy, and then copy the changes to the original.
In short, you don't need it. This code is equivalent:
Code: Select all
    Set PPSlide = PPPres.Slides(3)
    With PPSlide.Shapes(1)
        .Height 200
        .Width 500
    End With

Will the code run? I don't know. However, logically, at least it has a chance but, as I said, I am not familiar with VBA for PowerPoint.
Code: Select all
Public Sub TryMe()
    Dim PPapp As PowerPoint.Application
    Dim PPPres As Presentation
    Dim PPSlide As Slide
    Dim Ws As Excel.Worksheet

    Set PPapp = GetObject(, "PowerPoint.Application")
    Set PPPres = ActivePresentation
    Set PPSlide = PPPres.Slides(3)
    Set Ws = ActiveSheet
   
    Ws.Range("A1:D20").Copy
    PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, _
                                Link:=msoTrue
    With PPSlide.Shapes(1)
        .Height 200
        .Width 500
    End With
    Set PPapp = Nothing
End Sub
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: VBA resizing a cell range and chart linked from Excel in PPT

Postby Berta11 » Thu Apr 21, 2016 4:13 am

It will not change your work in PowerPoint...I've used this great tool in my college when I was a student. I needed to make a lot of presentations so I downloaded animated templates from http://www.pptstar.com/powerpoint/animated/, that was so easy :lol:
  • 0

Berta11
Rookie
 
Posts: 1
Joined: Apr 21, 2016
Reputation: 0
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 44 guests