Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Picture.Insert dilemma

Status
Not open for further replies.

Codman

Technical User
Joined
Nov 25, 2003
Messages
44
Location
GB
I have a pice of code that works well to place a .Gif file to a specific location in a spreadsheet. However, I need to change the directory dependant upon a user path and am having some problems. I obtain the path via a user form and dump this to an excell cell, but if I try to reference that cell in place of the C:\My Documents\Gallery\ it fails.

Here's the original code:-

[P17].Select
Set Gif = ActiveSheet.Pictures.Insert( _
"C:\My Documents\Gallery\Shapes.gif")
With Gif.ShapeRange
.IncrementLeft -1.5
.IncrementTop -1.5
.ZOrder msoSendToBack
End With
Sheet4.Select

If I then change it to the following it also fails as an "Unable to get insert property of the picture class"

With Sheet4.[A6].Select
Dim Gif As Object
Dim x As Integer
Dim i As Integer
Dim Z As String
Z = Sheet2.Cells(44, 3)

Set Gif = ActiveSheet.Pictures.Insert(Z)
With Gif.ShapeRange
.IncrementLeft 50
.IncrementTop 0
End With
End With

Can anyone give me a clue please?

CodMan
 
Sub insert_picture()
Dim x As Integer
'
x = ActiveSheet.Shapes.Count
If x > 1 Then 'I have a radio button on the sheet that is shape(1)
ActiveSheet.Shapes(x).Select
Selection.Delete
Cells(1, 1).Select'anchors the picture in cell A1
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\My Documents\My Pictures\car.jpg").Select
Else
End If
Cells(1, 10).Select
End Sub
 
Hi ETID,

The code I have works OK, but it's this line that's casing me problems:-

ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\My Documents\My Pictures\car.jpg").Select



I need to be able to set the path from outside the VBA environment i.e from the data in a cell such as C:\MyDocuments\Car.Jpeg, but when I try this I get the picture class error. If I have the path hard coded it's OK. Any ideas??


CodMan
 
Have you tried this ?
Z = Sheet2.Cells(44, 3).Value
or this ?
Z = Sheet2.Cells(44, 3).Text

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Have you confirmed that the file path and variable that is being passed from the form is complete and formatted as a string?

I.E. all slashes and the .gif extension.
 
Hi Guy's

I've tried passing the cell text to a message box successfully using msgBox Sheet2.Cells(44, 3) with both the text and value extension. However, I still cannot get it to run without the "Unable to get insert property of the picture class" error

Public Sub Diag_Pub()
Call Table_copy
With Sheet4.[A6].Select
Dim Gif As Object
Dim x As Integer
Dim i As Integer


z = Sheet2.Cells(44, 3).Text

Set Gif = ActiveSheet.Pictures.Insert(z)
With Gif.ShapeRange
.IncrementLeft 50
.IncrementTop 0
End With
End With
 
ETID,

I tried your code with a slight modification as below. This also gives me the same problems.It looks as though the Pictures.Insert (" ") command wont accept a placeholder. If this is so can anyone suggests an alternative approach.

Sub insert_picture()
Dim x As Integer
Dim z As String

z = Sheet2.Range("A39").Value Tried .Text too!

'
x = ActiveSheet.Shapes.Count
If x > 1 Then 'I have a radio button on the sheet that is shape(1)
ActiveSheet.Shapes(x).Select
Selection.Delete
Cells(4, 1).Select 'anchors the picture in cell A1
ActiveSheet.Pictures.Insert(z).Select
Else
End If
Cells(4, 10).Select
End Sub

Where: "A39" = "C:\Documents and Settings\My Documents\My Pictures\base_swirl.gif

CodMan
 
Have you check the length of the string for leading or trailing non visible characters?
 
I noticed in your example above that the file extension is .JPEG instead of .JPG ...just a thought
 
Hi ETID

I looked for spaces and also invalid characters but this didn't help. Testing this further I've tried changing the file extension to jpg and also gif. Again no joy. I think the insert property is the problem. I used debug.print to ascertain that the cell text was correct, but it failed. If I then substitute the cell data Z calls up, "C:\Documents and Settings\My Documents\My Pictures\base_swirl.gif" text for Z, all works OK. I may need to change the method in which I insert but even the addPicture method fails. Any idea's would be welcome.


Public Sub Test_Pic()
With Sheet4.[A6].Select
Dim Gif As Object
Dim x As Integer
Dim i As Integer
Dim Z As String


Z = Sheet2.Range("C44").Text
Debug.Print Z

Set Gif = ActiveSheet.Pictures.Insert(Z)

With Gif.ShapeRange
.IncrementLeft 50
.IncrementTop 0
End With
End With

CodMan
 
I had no problems running this...

Also...I added code (rem-ed out in the example) that clears out the previous picture (if any).

The only time I got the error "Unable to get insert property of the picture class" was when the path and/or picture name was incorrect or did not exist.

The only thing I can suggest at this point is to try changing Z = Sheet2.Range("C44").Text to Z = trim(Sheet2.Range("c44").Text)... to strip out any phantom characters.





Public Sub Test_Pic()
With Sheet4.[A6].Select
Dim Gif As Object
Dim x As Integer
Dim i As Integer
Dim Z As String

'x = ActiveSheet.Pictures.Count

'If x > 0 Then
'ActiveSheet.Pictures(x).Select
' Selection.Delete
'Else
'End If

Z = Sheet2.Range("C44").Text

Debug.Print Z

Set Gif = ActiveSheet.Pictures.Insert(Z)

With Gif.ShapeRange
.IncrementLeft 50
.IncrementTop 0
End With
End With
End Sub


 
I've copied your code with the Rem'd out items still Rem'd and find no chnge to my problem. Could it be a PC specific issue. i.e some setting or option that need altering??

CodMan
 
...just fishing, but maybe a reference library needs to be loaded.

from the vba editor...

Tools>References...

I have the following checked:

visual basic for applications
microsoft excel 10.0 object lib.
ole automation
microsoft office 10 object lib.
 
A good thought, but alas not successful. I'm running Windows XP pro and Office2000 and my reference library's are Micro soft Excel 9.0 lib etc.

I've now re-booted, started a new workbook and still the same problem.

Thanks ETID, any more help would save my bacon!

CodMan
 
I wonder if "GIF" is a reserve word...
as in the declaration "Dim Gif As Object"

maybe call this variable "GIF_" instead.

...or could there be protection or cell locks activated in the active sheet?
 
Hi ETID

I got it sorted. A collegue suggested the following changes

Public Sub Diag_Pub()
'Call Table_copy
With Sheet4.[A6].Select
Dim Gif As Object
Dim x As Integer
Dim i As Integer
Dim Z As String
Dim myPic As String

myPic = Range("C44").Value
Debug.Print myPic

Set Gif = ActiveSheet.Pictures.Insert(MyPic)
With Gif.ShapeRange
.IncrementLeft 50
.IncrementTop 0 ................


This worked on all the graphics.

Hope this helps somebody else.

Thanks again for your help.

CodMan
 
Hmmm...that's still a head scratcher as to why it works fine for me and not you.

but, glad to hear it's working for you.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top