PDA

View Full Version : Image path (url) in cell A1



ferverx
11-23-2005, 07:46 AM
Hello!

I have image form (made via control toolbox - =EMBED("Forms.Image.1";"") ) and I want it to use URL sated in cell A1 (URL represents the picture from WEB) which when changed updates the picture in Image box? I have no idea :think: of hoe to do it?

Thanx.

mvidas
11-23-2005, 09:46 AM
Hi ferverx,

Welcome to vbax, glad to see a Latvian here!

There is a way to do this, but it may not be what you're looking for. It would use VBA to download the picture to your hard drive, insert it from there, then delete the file. I'm not aware of another way to do that. Would this be an option for you?

If so, paste the following into the worksheet object code pane:Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim ImageLoc As String, TempImageFile As String
ImageLoc = Range("A1").Text
TempImageFile = "C:\temp" & Right(ImageLoc, 4)
SaveWebFile ImageLoc, TempImageFile
Image1.Picture = LoadPicture(TempImageFile)
Kill TempImageFile
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
Set oXMLHTTP = CreateObject("msxml2.xmlhttp")
oXMLHTTP.Open "GET", vWebFile, False
oXMLHTTP.Send
Do While oXMLHTTP.ReadyState <> 4
DoEvents
Loop
oResp = oXMLHTTP.ResponseBody
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
Set oXMLHTTP = Nothing
End FunctionAtta!
Matt

ferverx
11-24-2005, 12:35 AM
OK. at least something, it works, but the thing is that picture is opened witk photo editor, but I need it to open in Excel's image box? What could be done about that?

mvidas
11-28-2005, 08:04 AM
OK. at least something, it works, but the thing is that picture is opened witk photo editor, but I need it to open in Excel's image box? What could be done about that?The picture is opening with a photo editor? Hmmm, not sure why that would be, it should be opening in the image control (I have it named Image1), and not using anything external whatsoever. Since it works fine for me without anything external, and I can't figure out why it would, would you mind stepping through the code one line at a time (using F8) to see what line of code brings up the photo editor?

Shazam - I will be moving your post to it's own thread (since it is slightly different than ferverx's question, and deserves its own space too). You can find it at http://www.vbaexpress.com/forum/showthread.php?t=6201 and I will add my reply there shortly.

Matt