hello ez08mba,
I'm afraid there is very little I can do to help you.
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.
Now you use this object in "PPSlide.Shapes(1).Select". Why do you want to select it?
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