Solved

Get images from the URL's in excel

Posted on 2014-07-22
18
1,194 Views
Last Modified: 2014-07-23
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
0
Comment
Question by:mtthompsons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
18 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213441
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213451
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
 

Author Comment

by:mtthompsons
ID: 40213485
I want the images into the excel in Column "D" not just download to a drive
Will this code do that?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:duncanb7
ID: 40213517
Already done that, please check the attachment

Just run test() sub in macro1

Duncan
image.xls
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213521
Please review the code , you also need to know what is doing on each code

Duncan
0
 

Author Comment

by:mtthompsons
ID: 40213578
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213585
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213587
I work because I click the Range("D1') in sheet1  before macro start to run

Duncan
0
 

Author Comment

by:mtthompsons
ID: 40213641
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213770
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213773
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
 

Author Comment

by:mtthompsons
ID: 40213795
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
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213808
probaby it is Excel version issue , I am using 2003

you are using 2007 or above, right ?
0
 

Author Comment

by:mtthompsons
ID: 40213812
yes i am using 2007
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40213819
I am downloading Excel 2007, please wait since it is expired

Duncan
0
 
LVL 13

Accepted Solution

by:
duncanb7 earned 400 total points
ID: 40213901
see the attachment and run macro

Duncan
image.xls
0
 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 100 total points
ID: 40213947
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
 

Author Closing Comment

by:mtthompsons
ID: 40213995
Thanks a lot for the help...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question