How to Insert images in cells in BULK from a corresponding hyperlink

[Derek Fingleson]

Hi Guys

So here is my problem..
In Column A .. I have the hyperlinks of the images.. These are all on my local computer ( I have approx 7,000 rows of this )
I want to insert the image in the corresponding Cell in Column B
I can do it individually , ( Then have to resize image to say height 0.9" ) but this is crazy and time consuming..
How can I automate this ?

So for Example :-

Row Col A ... Col B
1 G:\Derek\Documents\RENDERED\ET 52 29 ST6.jpg Actual image
2. G:\Derek\Documents\RENDERED\ET 52 4VIEW.jpg Actual image
3. G:\Derek\Documents\RENDERED\ET 52 29 ST 5.jpg Actual image

 

[Saurabhj]

Hi,

Please check below code.

Sub loadPicture()

Dim Pic_Location As String, picNumber As Integer
Dim cellLocation As Range
Dim Pic_Name As String, totalRows As Integer

'To check totalRows in column A
totalRows = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For picNumber = 2 To totalRows
    Set cellLocation = ActiveSheet.Range("B" & picNumber)
    Pic_Location = ActiveSheet.Range("A" & picNumber)
    Set Pic = cellLocation.Parent.Pictures.Insert(Pic_Location)
    
    With Pic
        '.ShapeRange.LockAspectRatio = msoFalse
        .Top = Cells(picNumber, 2).Top
        .Left = Cells(picNumber, 2).Left
        .Height = 0.9 * 72
End With
Next

 

[Derek Fingleson]

Hi Saurabhj​

This is fantastic... Thank you.. It works !!

 

[Derek Fingleson]

Hi Saurabhj​

This is fantastic... Thank you.. It works !!

Hi Saurabhj

I thought it worked... BUT BUT..

There is a problem.. So what happens is :-
When you save the file , close excel etc.. and Then reopen the file , it then stats trying to reinsert all the images again !
Now, I have 7,000 rows , so this takes hours to even reload ! ( Im sure the cells contain the link to the image? )

Is there a way to format the cells to simply have the image and NOT the link . ( I need to remove any Hyperlink in the pic )
So , somehow , I just need the image with ZERO cross link to where it came from .. ie any hyperlinks etc..
Also , I have tried to use Row 1 as the headings and create a filter.
So now when you filter , it starts all over again looking for the links and bringing back the images !

In the format of the picture, I also need to set the property to "Move and Size with Cell"

What are your thoughts on this ?

Regards
Derek

 

[Saurabhj]

Hi, I hope you are not executing the code on Workbook Open event.

The function is not creating any hyperlink. If the images are loaded then it's loaded. It might possible due to large number of images, making excel slow.

After loading all images, what is the file size ?

 

[Derek Fingleson]

Hi, I hope you are not executing the code on Workbook Open event.

The function is not creating any hyperlink. If the images are loaded then it's loaded. It might possible due to large number of images, making excel slow.

After loading all images, what is the file size ?

I reduced file to 4000 rows.. Size now 580KB ... Was like 3 times the size before !
Everything now working as planned... Thank You

 

[Saurabhj]

That's great. Thanks for the feedback.