PDA

View Full Version : Excel 2003 Macro, no longer working in 2010



MRichmond
02-17-2014, 07:22 AM
I have a macro that works perfectly in Excel 2003, however now I have been "upgraded" to Excel 2010, and my macro no longer works as it should.

Would anyone be able to look at the code, and suggest changes to make it work in both versions please (some users are still on Excel 2003)


Sub insertpicture()
Dim sPicture As String, pic As Picture

'Opens dialogue box to be able to search for picture to insert

sPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif), *.gif; *.jpg; *.bmp; *.tif", _
, "Select Picture to Import")

If sPicture = "False" Then Exit Sub
'Resizes the picture to fit within the allotted area on the spreadsheet

Set pic = ActiveSheet.Pictures.Insert(sPicture)
With pic
.ShapeRange.LockAspectRatio = msoFalse
.Height = 135
.Width = 255
.Top = ActiveCell.Top
.Left = ActiveCell.Left
.Placement = xlMoveAndSize
End With

'Compresses the picture

Set pic = Nothing
Dim octl As CommandBarControl

Set octl = Application.CommandBars.FindControl(ID:=6382)
Application.SendKeys "%A%W~"
octl.Execute
End Sub


The code is designed to to open a dialogue box to enable the user to select the picture they wish to add to the spreadsheet. Once selected it resizes the picture to fit a cell range. Finally it compresses the picture (our guys have a habit of trying to put 3mb pictures on this form).

When I say it doesnt work any longer, what it appears to be doing is actually inserting a link to the picture, which looks ok on the users PC, but when it arrives in someone else's inbox, the pictures have been replaced by links.

Bob Phillips
02-17-2014, 09:03 AM
What is control ID 6382? Does it still exist with the Ribbon?

mancubus
02-18-2014, 12:02 AM
hi. have seen stg like this:
Application.SendKeys "%(oe)~{TAB}~"
Application.CommandBars.ExecuteMso "PicturesCompress"

MRichmond
02-18-2014, 01:32 AM
Thanks Mancubus. I've replaced the two lines with your suggestions however now the macro wont run at all, it highlights the Name?? Any suggestions?

XLD Control ID 6382 is the Compress Picture dialogue box, and whilst I can find it on the ribbon I'm assuming it's been replaced by the line Mancubus has supplied.

mancubus
02-18-2014, 02:18 AM
http://answers.microsoft.com/en-us/office/forum/office_2007-customize/compress-pictures-in-ppt2007/c79cd148-2a52-421f-856a-6bd908469f1a