Excel 2007/Vista - PastePicture Problem
Peter you star, Thank you!
As it happens I'd adapted Stephen's code to copy a number of
individual cells (simple coloured squares), rather than a chart. I
then use the small coloured bitmaps against a custom Status menu's
controls - a sort of traffic light system.
Using the insight you gave me I created coloured 'shapes' to copy, as
an alternative to the coloured cells and used 'Copy' rather than
'CopyPicture'. Curiously the straight forward Copy method doesn't work
in Excel 2003 so, for dual compatibility, my code to call the
PastePicture function now looks like this...
(Beware of wrap-around)
Public Sub CreateStatusKeyBitmaps()
Dim i As Long
Dim oPic As IPictureDisp
For i = 0 To 5
If Application.Version < 12 Then
'Excel 2003 code
If wksParams.Range("Status" & i).CopyPicture(xlScreen,
xlBitmap) Then
Set oPic = PastePicture(xlBitmap)
SavePicture oPic, Environ("TEMP") & "\Status" & i &
".bmp"
End If
Else
'Excel 2007 code
wksParams.Shapes("Rectangle " & i).Copy
Set oPic = PastePicture(xlBitmap)
SavePicture oPic, Environ("TEMP") & "\Status" & i & ".bmp"
End If
Next i
End Sub
....Elsewhere, triggered by a Worksheet_Activate() event, another
routine loads these saved bitmaps against the afore-mentioned menu
items as the menu is created on the fly for certain sheets. The menu
is deleted by the Worksheet_Deactivate() or Workbook_WindowDeactivate
() events so that it doesn't appear in the wrong context.
I hope someone else finds this useful. Not much consolation here for
those that need to create a bitmap from a range I'm afraid.
Br, Nick H
|