Image path (url) in cell A1

[ferverx]

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 of hoe to do it?

Thanx.

[mvidas]

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 Function

Atta!
Matt

[ferverx]

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]

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