jhattingh
asked on
Excel VBA: How to get an image from a url that redirects?
if strContent = "http://url_to_some_image.jpg", then the following code works fine:
ActiveSheet.Pictures.Inser t(strConte nt).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
ActiveSheet.Pictures.Inser
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
ASKER
tricky... my example is on our intranet :|
I'll see what I can find as an external example.
I'll see what I can find as an external example.
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.
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
ASKER
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)
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
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.
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.
ASKER
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
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
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.
Rob.
ASKER
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?
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.
So if you plug that url straight into the IE browser, what happens?
Rob.
ASKER
bags... it didn't work.. not only that, but the web app doesn't support IE :|
still thinking...
still thinking...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, thanks for the additional attempt. I get exactly the same response.. it isn't remembering that I am "logged in".
ASKER
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...
ASKER
Thanks for your input.
I'm not sure which approach I'll try just yet....
Rob.