How to "Reset Picture" in Power point Slide using VBA macro?

0
votes

I am generating power point presentation using Excel. The macro is running excel. It is working perfectly fine. Issues is the Excel macro pasted the pictures with strange format. I have to use manually the command of "Reset Picture" by right clicking on each picture-->Picture format--> Reset picture

Is it possible to make a macro power point which can do the reset picture command automatically for me?

THanking you advance Best regards

 'getting name of picture from Excel sheet cell

 logopic = ThisWorkbook.Sheets("Jan 2015").Range("z" & CellNr).Value
 apic = ThisWorkbook.Sheets("Jan 2015").Range("aa" & CellNr).Value
 mpic = ThisWorkbook.Sheets("Jan 2015").Range("ab" & CellNr).Value

If ThisWorkbook.Sheets("Jan 2015").Range("z" & CellNr) > 0 Then

' here we are copying the pictures of logo in the respective slides

 oPP1.Slides(2).Shapes.AddPicture("" & FolderPath & "\" & logopic &    ".jpg", msoFalse, msoCTrue, 10, 10, 60, 45).Apply
2
imagevbaexcelpowerpoint
Would help to show the code which is doing the pasting into PPT – Tim Williams
As Tim says, show us the current code you've got. And it might help to explain what you mean by "strange format". Wild guess, though: if you're using code to add a picture shape by inserting a picture from a file, use -1, -1 as the height and width coordinates. That might be all you need. – Steve Rindsberg
THanks ...here is the code: – haplc
@haplc as Steve pointed, use -1,-1 for the height and width parameter then the image will maintain the aspect ratio. I don't understand why you have Apply call since that applies the a formatting that has been picked up. You can remove that as well if it is not intentional. – Shyam Pillai

2 Answers

1
votes

Record the steps in powerpoint's macro recorder. That will give you the basic code to paste into your excel macro. Alt T, M, R to start, same keys to stop recording. Then Alt + F11 to open Powerpoint's VBA editor to find the code.

Also have a look at Paste Special command where you choose the format to be pasted.

This is an answer I give about doing the same in VBA - VBS. For you it's easier as it's VBA - VBA. Make sure powerpoint in in the References in Excel.

Record the steps in excel macro recorder. You have to rewrite it a bit because it uses a type of syntax that vbs doesn't.

This applies (I don't have a medium9) xlRangeAutoFormatAccounting4 in vba.

Selection.AutoFormat Format:=xlRangeAutoFormatAccounting4, Number:=True, _
    Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

So first look up constants in vba's object browser. xlRangeAutoFormatAccounting4 = 17

Then look the function up in object browser and look at the bottom for the function definition,.

Function AutoFormat([Format As XlRangeAutoFormat = xlRangeAutoFormatClassic1], [Number], [Font], [Alignment], [Border], [Pattern], [Width])

So the vba becomes in vbs (and vbs works in vba) (and as you can see you can work out the correct way without needing to look the function up usually)

Selection.AutoFormat 17, True, True, True,True, True, True

So your code becomes

objXLWs.Range("A3").CurrentRegion.Select.AutoFormat 17, True, True, True,True, True, True

You are using Excel and you can record it in Excel and have Excel write your code.

Alt + T, M, R

then Home key then Up Arrow. Stop recording.

Look what Excel wrote

Selection.End(xlUp).Select

or if you had of recorded Go To dialog

Application.Goto Reference:="R1C1"

or if you had of recorded Ctrl + Home

Range("A1").Select
0
votes
Sub InsertPictureExample()

    ' This inserts a picture at "natural" size on
    ' Slide 1 of the current active presentation

    Dim oSh As Shape

    With ActivePresentation.Slides(1)
        Set oSh = .Shapes.AddPicture("c:\temp\thing.png", msoFalse, msoTrue, 0, 0, -1, -1)
        ' position the shape if desired:
        oSh.Left = 100
        oSh.Top = 100
    End With

End Sub