Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

LOAD AN IMAGE FROM AN URL ADDRESS ON A USERFORM OBJECT

Posted on 2014-02-18
5
Medium Priority
?
3,057 Views
Last Modified: 2014-02-19
VBA EXCEL 2010
Userform

Is there a control in Excel other then the "image control" where i could load a picutre from a url address ...?

or someother way to display an image from a url address ?

Thanks
fordraiders
0
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39869487
The most simple way is to use the web browser control.
For example:
Private Sub UserForm_Initialize()
    WebBrowser1.Navigate ("http://www.indexoncensorship.org/wp-content/uploads/2012/09/EU_Flag.jpg")
End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39869572
The other option is to use a image control, download the file to local disk and then load it in the image control.

Put this in a standard module:
Option Explicit

Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, _
 ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" _
(ByVal hOpen As Long, ByVal sUrl As String, ByVal sHeaders As String, _
 ByVal lLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long

Public Declare Function InternetReadFile Lib "wininet.dll" _
(ByVal hfile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, _
 ByRef lNumberOfBytesRead As Long) As Integer

Public Declare Function InternetReadBinaryFile Lib "wininet.dll" Alias "InternetReadFile" _
(ByVal hfile As Long, ByRef bytearray_firstelement As Byte, ByVal lNumBytesToRead As Long,
 ByRef lNumberOfBytesRead As Long) As Integer

Public Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer

Public Declare Function HttpQueryInfo Lib "wininet.dll" Alias "HttpQueryInfoA" (ByVal hOpen As Long, ByVal infotype As Long, _
 ByVal buffer As String, ByRef bufferlength As Long, ByVal Index As Long) As Long

Public Const NO_ERROR = 0             ' No error
Public Const INTERNET_FLAG_RELOAD = &H80000000
Public Const INTERNET_OPEN_TYPE_PRECONFIG = 0

' Call function with URL and file name
Public Function getBinaryFile(url As String, fileName As String) As Boolean
    Dim hOpen As Long
    Dim hOpenUrl As Long
    Dim retval As Long
    Dim fileSize As Long
    Dim byteArray() As Byte
    Dim bufflen As Long
    Dim databuff As String * 8
    Dim bytesread As Long
    Dim intFile As Integer
    Dim isOpen As Boolean

    On Error GoTo errhandler

    bufflen = Len(databuff)
    hOpen = InternetOpen("myconn", INTERNET_OPEN_TYPE_PRECONFIG, 0, 0, 0)
    hOpenUrl = InternetOpenUrl(hOpen, url, 0, 0, INTERNET_FLAG_RELOAD, 0)

    If hOpenUrl > 0 Then
        retval = HttpQueryInfo(hOpenUrl, 5, databuff, bufflen, 0)

        If retval > 0 Then
            fileSize = databuff

            ReDim byteArray(fileSize + 1) As Byte

            retval = InternetReadBinaryFile(hOpenUrl, byteArray(0), fileSize, bytesread)
            If (retval > 0) Then
                intFile = FreeFile

                Open fileName For Binary As #intFile
                isOpen = True
                Put #intFile, , byteArray
                Close #intFile

                isOpen = False
            End If
        End If
    End If

    getBinaryFile = True

errhandler:
    If Err.Number <> 0 Then
        getBinaryFile = False
    End If

    On Error Resume Next
    If hOpenUrl > 0 Then InternetCloseHandle (hOpenUrl)
    If hOpen <> 0 Then InternetCloseHandle (hOpen)

    If isOpen Then Close #intFile
End Function

Open in new window


And this in the userform:
Private Sub UserForm_Initialize()
    Dim url_path As String
    Dim file_path As String
    
    url_path = "http://www.indexoncensorship.org/wp-content/uploads/2012/09/EU_Flag.jpg"
    file_path = ThisWorkbook.Path & "\" & "Image.png"
    
    ' Download picture
    getBinaryFile url_path, file_path

    ' Load picture
    With Image1
        .Picture = LoadPicture(file_path)
    End With
    
    ' Delete Saved file
    Kill file_path
End Sub

Open in new window

0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 39869584
Another way you can download the picture is with this code (replace the code in the standard module with this):
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

Public Function DownloadFilefromWeb(url As String, fileName As String) As Boolean
    DownloadFilefromWeb = URLDownloadToFile(0, url, fileName, 0, 0)
End Function

Open in new window


And the code in the userform with this:
Private Sub UserForm_Initialize()
    Dim url_path As String
    Dim file_path As String
    
    url_path = "http://www.indexoncensorship.org/wp-content/uploads/2012/09/EU_Flag.jpg"
    file_path = ThisWorkbook.Path & "\" & "Image.png"
    
    ' Download picture
    DownloadFilefromWeb url_path, file_path

    ' Load picture
    With Image1
        .Picture = LoadPicture(file_path)
    End With
    
    ' Delete Saved file
    Kill file_path
End Sub

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39870061
perfect Thanks very very much !
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39870111
You're welcome.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

660 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