Get images from the URL's in excel

Hi All,

Attached file is a sample. I have the URL's in Column "C" which are of images. I want help with a macro that can check the URL and fetch the images and paste into column "D"

Can anyone help me please as i have 100's to be gathered

Thanks
Sample.xls
mtthompsonsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncanb7Commented:
You need download function URLDownloadToFile on vba to do,
run the macro1 test() as follows, and save image files at C;\temp\

Hope understand your question.if not, please point it out

Duncan

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Private Const ERROR_SUCCESS As Long = 0



Sub test()

Dim tmp, i As Integer
Dim slocalfile As String
'Delete all image
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
Pic.Delete
Next Pic

'import image
tmp = Range(Range("C1"), Range("C65535").End(xlUp)).Count
Debug.Print "Starting loading and importing image"
For i = 1 To tmp
Debug.Print Cells(i, 3).Value
slocalfile = "C:\temp\c" & i & ".png"
Call URLDownloadToFile(0&, Cells(i, 3).Value, slocalfile, 0&, 0&)
 Cells(i, 4).Select
    Selection.ClearContents
    ActiveSheet.Pictures.Insert(slocalfile).Select
    
Next i

End Sub

Open in new window

0
duncanb7Commented:
be reminded you need to  have c:\temp directory to save the image locally, otherwise change it to other directory you want on VBA

Duncan
0
mtthompsonsAuthor Commented:
I want the images into the excel in Column "D" not just download to a drive
Will this code do that?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

duncanb7Commented:
Already done that, please check the attachment

Just run test() sub in macro1

Duncan
image.xls
0
duncanb7Commented:
Please review the code , you also need to know what is doing on each code

Duncan
0
mtthompsonsAuthor Commented:
I get the item with the specified name is not found

I do have a folder called temp in C Drive

When debug goes to this line
    ActiveSheet.Shapes("Picture 1").Select
0
duncanb7Commented:
it might be you did not select sheet and cell so that there is not Activate

put those two code into test()
ActiveWorkbook.Sheets("sheet1").Activate
ActiveWorkbook.Sheets("sheet1").Range("D1").Select

Duncan

Sub test()
Dim tmp, i As Integer
Dim slocalfile As String
'Delete all image
Dim Pic As Object
For Each Pic In ActiveSheet.Pictures
Pic.Delete
Next Pic
ActiveWorkbook.Sheets("sheet1").Activate
ActiveWorkbook.Sheets("sheet1").Range("D1").Select
'import image
tmp = Range(Range("C1"), Range("C65535").End(xlUp)).Count
Debug.Print "Starting loading and importing image"
For i = 1 To tmp
Debug.Print Cells(i, 3).Value
slocalfile = "C:\temp\" & i & ".png"
Call URLDownloadToFile(0&, Cells(i, 3).Value, slocalfile, 0&, 0&)
 Cells(i, 4).Select
    Selection.ClearContents
    ActiveSheet.Pictures.Insert(slocalfile).Select
    
Next i

End Sub

Open in new window

0
duncanb7Commented:
I work because I click the Range("D1') in sheet1  before macro start to run

Duncan
0
mtthompsonsAuthor Commented:
I ran the macro called Test and selected sheet 1 column "D" but all images are being collected but all one over the other in column "A"
0
duncanb7Commented:
just run the attachment , by click atl-F11 go to VBA on Excel and run test() macro and no need to do any select( it is already done for you).

review all my previous post and think about each code function


Duncan
image.xls
0
duncanb7Commented:
Please read it, Tutorial for Excel VBA for activate and select  at
http://msdn.microsoft.com/en-us/library/office/aa221576(v=office.11).aspx
0
mtthompsonsAuthor Commented:
Thanks
Everything works fine
its just that it copies all images on each other and not d column
Please see the attachment
Sample.JPG
0
duncanb7Commented:
probaby it is Excel version issue , I am using 2003

you are using 2007 or above, right ?
0
mtthompsonsAuthor Commented:
yes i am using 2007
0
duncanb7Commented:
I am downloading Excel 2007, please wait since it is expired

Duncan
0
duncanb7Commented:
see the attachment and run macro

Duncan
image.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MacroShadowCommented:
Pictures can never be placed in a cell, the picture layer is a  separate layer. You can place an image over a cell which I believe Duncan is doing (I didn't check any of his samples).

Check the attached file, in column D the image is inserted in a comment and in column E the image is placed over the cell.

Look at the formulas in columns D & E to see how it works. It is possible if you want to create a macro that will insert the images instead of manually inserting formulas.
Sample.xls
0
mtthompsonsAuthor Commented:
Thanks a lot for the help...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.