Posted by: yuvan004 | December 16, 2011

INSERTING AND DELETING THE IMAGE IN EXCEL THROUGH VBA MACRO


INSERTION:

If you have image list in excel sheet you have to insert that image means you can use the following syntax

c38 = ThisWorkbook.Worksheets(“Input”).Cells(3, 69)

InsertPictureInRange “C:\images\” + c38 + “.jpg”, _
Range(“D216:F228”)

Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
‘ inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> “Worksheet” Then Exit Sub
If Dir(PictureFileName) = “” Then Exit Sub
‘ import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
‘ determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left – .Left
h = .Offset(.Rows.Count, 0).Top – .Top
End With
‘ position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

This for deleting the image in particular range

Dim Sh As Shape
With Worksheets(“report”)
For Each Sh In .Shapes
If Not Application.Intersect(Sh.TopLeftCell, .Range(“b215:p296”)) Is Nothing Then
If Sh.Type = msoPicture Then Sh.Delete
End If
Next Sh
End With

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: