Solved

Excel VBA: How to get an image from a url that redirects?

Posted on 2008-06-16
14
1,473 Views
Last Modified: 2011-10-19
if strContent = "http://url_to_some_image.jpg", then the following code works fine:

ActiveSheet.Pictures.Insert(strContent).Select

But I now need to insert images from urls that redirect to an image!

if this is not possible, then I can alternatively call a url that returns (as page text) the full url. In which case my question is: how do I read the text of a page in an Excel VBA macro?

(I would much rather that the first option (using a redirecting url) were used, though.

Thank you in advance,

jason
0
Comment
Question by:jhattingh
  • 8
  • 6
14 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 21798299
Hi, can you please provide an example of a link from urls that redirect to an image?

I'm not sure which approach I'll try just yet....

Rob.
0
 
LVL 1

Author Comment

by:jhattingh
ID: 21800216
tricky... my example is on our intranet :|

I'll see what I can find as an external example.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21819681
Hi, any luck finding an example?  Perhaps you could run this VBS code against your redirection URL for me, and see what the responseText is...we might be able to extract the target image URL out of that....

Regards,

Rob.
strURL="http://yourintranet/index.html"
 

Set objHTTP = CreateObject("MSXML2.XMLHTTP") 

objHTTP.Open "GET", strURL, FALSE

objHTTP.Send

strPageText = objHTTP.responseText
 

WScript.Echo strPageText

Open in new window

0
 
LVL 1

Author Comment

by:jhattingh
ID: 21820437
Hi,

I translated your vbScript into something excel could do (see attached snippet).

The response was:

"<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/1999/REC-html401-19991224/loose.dtd">
<html lang="en">
<head>
      <meta http-equiv="content-type" content="text/html; charset=utf-8">
      <title>shotgun</title>
      <


(not sure why it is truncated, but for now this probably doesn't matter)
Sub TestThumb()

'

' TestThumb Macro

' Macro recorded 19/06/2008 by slam

'

' Keyboard Shortcut: Ctrl+i

'

Dim oHTTP As New MSXML2.XMLHTTP
 

strUrl = "http://shotgun/thumbnail/image/65090"
 
 

oHTTP.Open "GET", strUrl, False

oHTTP.send
 

strResponse = oHTTP.responseText
 

End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21828053
Hi, the response text is probably truncated because you're outputting the result with MsgBox.  This has an output character limit of 1024 characters.

If you use the VBS code I posted, it should output the entire contents.  You need to look in the code to see if there is any link to the target image you're after...

Save the code I posted into Notepad, then "Save As" a file with a vbs extension, then just double click it.

Regards,

Rob.
0
 
LVL 1

Author Comment

by:jhattingh
ID: 21932112
sorry it took so long.

The vbscript generated a msgbox and I coun;t copy/paste.

I've tried to attach a screenshot...

Looking forward to your feedback
ss.GIF
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21938854
Hmmmm, it looks like that URL goes to a login screen.  Do you need to login before you can access the image that it goes to?

Rob.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:jhattingh
ID: 21939573
The crazy thing is that if I use the url directly, I don't get asked for credentials... hmmmm... actually, I need to re-run the code. I *think* it works fine when I do it manually because I used firefox and the cookies must have already stored the credentials, etc. Maybe when using the script, it was "using IE" so I should first test it with IE manually and then run the script... what do you think?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 21941933
Yeah, try that....because I can only script this with IE, we should work with that....

So if you plug that url straight into the IE browser, what happens?

Rob.
0
 
LVL 1

Author Comment

by:jhattingh
ID: 21943432
bags... it didn't work.. not only that, but the web app doesn't support IE :|

still thinking...

0
 
LVL 65

Accepted Solution

by:
RobSampson earned 250 total points
ID: 21949795
Yeah, I sorta figured it wouldn't do IE judging by the screenshot you posted.  It says to click a link to ignore the browser check to continue.....

Try this VBS, although I don't think it will work without knowing the direct image link....

Rob.
Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")
 

strURL = "http://shotgun/thumbnail/image/65090"

strLocalFile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & Mid(strURL, InStrRev(strURL, "/") + 1)
 

objXMLHTTP.open "GET", strURL, False

objXMLHTTP.send()
 

If objXMLHTTP.Status = 200 Then

      Set objADOStream = CreateObject("ADODB.Stream")

      objADOStream.Open

      objADOStream.Type = 1 'adTypeBinary

      

      objADOStream.Write objXMLHTTP.ResponseBody

      objADOStream.Position = 0    'Set the stream position to the start

      

      Set objFSO = Createobject("Scripting.FileSystemObject")

      If objFSO.Fileexists(strLocalFile) Then objFSO.DeleteFile strLocalFile

      Set objFSO = Nothing

      

      objADOStream.SaveToFile strLocalFile

      objADOStream.Close

      Set objADOStream = Nothing

End If
 

Set objXMLHTTP = Nothing

MsgBox "Please see if " & strLocalFile & " was created."

Open in new window

0
 
LVL 1

Author Comment

by:jhattingh
ID: 21951856
Hi, thanks for the additional attempt. I get exactly the same response.. it isn't remembering that I am "logged in".


0
 
LVL 1

Author Comment

by:jhattingh
ID: 21996853
I finally have a url that doesn't redirect. My problem more consistently now is: using msxml object, IE is being detected as the 'browser' and the web app doesn't support it. I see that my need for an answer is now leading me away from the original question...
0
 
LVL 1

Author Closing Comment

by:jhattingh
ID: 31467484
Thanks for your input.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now