URL/HYPERLINK

[samboy]

I have urls/hyperlinks in a column of my spreadsheet linking to my suppliers
image server? how do I retrieve the iamges and save the image in a file and
place the jpeg/gif name in an adjacent cell. there are approximatly 15000
url. Can it be automated.

 

[Chip Pearson]

The following code should get you started in the right direction. It
will download the file that is the target of the hyperlink in A1 to
the folder named in FolderName.


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

Sub AAA()
Dim FolderName As String
Dim URL As String
Dim FName As String

FolderName = "C:\Test" '<<< CHANGE
URL = Range("A1").Hyperlinks(1).Address '<<< CHANGE
FName = FolderName & "\" & Mid(URL, InStrRev(URL, "/") + 1)
URLDownloadToFile 0&, URL, FName, 0&, 0&
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[samboy]

If I got this correct you want to place the target file to receive the image
between the quote marks at (FolderName = "C:\Test" '<<< CHANGE) and the
hyperlink range D215000

Anything else in the equation I need to add?


Thanks for the tip, that helps greatly.

 

[samboy]

This is how I modified what you gave me, it worked to download the image from
hyperlink in "D2" but none of the others. I am not sure why, I specified the
range thru D12. Any further clue. I am excited it did the one. THANKS!

Below is how modified the macro.


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

Sub AAA()
Dim FolderName As String
Dim URL As String
Dim FName As String

FolderName = "C:\Test" '<<< CHANGE
URL = Range("D212").Hyperlinks(1).Address '<<< CHANGE
FName = FolderName & "\" & Mid(URL, InStrRev(URL, "/") + 1)
URLDownloadToFile 0&, URL, FName, 0&, 0&