Automatically update an image from a web server in Excel

Automatically update an image from a web server in Excel

from Excel Ticker by Mourad Louha

A user asked in my favorite German forum "Office Lösung" about MS Office, if it is possible to automatically update an image from a web server in Excel. The user also posted some sample links and wished that the update should occur every minute.

The images from the web server are sent to the browser as raw data and therefore no HTML code is used. By the way, this can be for example achieved in PHP by calling the functions "header()" and "readfile()" in a specific order and setting the arguments to appropriate values. Unfortunately, we cannot use an Excel web query for linking to the image as the query will return unusable data. So, another solution must be found in this case.

As the user whished an automatic update, the best way in my opinion to do this, is to use VBA. The Excel VBA method "Application.OnTime()" allows to call another method in a specified interval. And, as a rectangle also allows specifying a background image, the solution is relatively simple. A sample file can be downloaded at the end of this article. Of course, Excel may have access to the internet and, for example, not to be blocked by a firewall.

Firstly, I inserted a new rectangle from the Drawing toolbar into the worksheet "Example" and then renamed the rectangle from "Rectangle 1" to "Picture" in the Name box. If you are using a newer Excel version, like Excel 2007 or 2010, you can insert a rectangle by clicking on the ribbon tab "Insert" and then on the button "Shapes". In a next step, I inserted a form control button into the sheet, which will be later linked to the VBA macro. And finally, I used the cell E5 for the internet URL to the image.

Then, I switched to the VBA Editor (Alt + F11) and created a new module to which I added the following code:

Option Explicit ' ' Constants... ' ' ........................................................................ Private Const mlchSheet As String = "Example" Private Const mlchPicture As String = "Picture" Private Const mlchButton As String = "Update" Private Const mlchInterval As Long = 15 Private Const mlchAddress_X As Long = 5 Private Const mlchAddress_Y As Long = 5 Private Const mlchAuto_X As Long = 16 Private Const mlchAuto_Y As Long = 5 ' ' Variables... ' ' ........................................................................ Public mlvpAutomatic As Boolean Public mlvpTime As Double ' ' Private... ' ' ........................................................................ Private Function mlfhTimer() Dim f As FillFormat ' Errors... On Error Resume Next ' Object... Set f = ThisWorkbook.Worksheets(mlchSheet) _ .Shapes(mlchPicture).Fill ' Check... If Not f Is Nothing Then f.UserPicture ThisWorkbook.Worksheets(mlchSheet) _ .Cells(mlchAddress_Y, mlchAddress_X).Value End If ' Check... If mlvpAutomatic Then ' Next... mlvpTime = Now + TimeSerial(0, 0, mlchInterval) ' Timer... Application.OnTime mlvpTime, "mlfhTimer" End If End Function ' ' Public... ' ' ........................................................................ Public Sub mlfpUpdate() ' Check... If mlvpAutomatic Then ' Update... mlvpAutomatic = False Application.OnTime mlvpTime, "mlfhTimer", , False ThisWorkbook.Worksheets(mlchSheet) _ .Cells(mlchAuto_Y, mlchAuto_X).Value = "Auto is off" ThisWorkbook.Worksheets(mlchSheet).Shapes(mlchButton) _ .OLEFormat.Object.Caption = "On" Else ' Update... mlvpAutomatic = True mlvpTime = Now + TimeSerial(0, 0, mlchInterval) ' Timer... Application.OnTime mlvpTime, "mlfhTimer" ' Update... ThisWorkbook.Worksheets(mlchSheet) _ .Cells(mlchAuto_Y, mlchAuto_X).Value = "Auto is on" ThisWorkbook.Worksheets(mlchSheet).Shapes(mlchButton) _ .OLEFormat.Object.Caption = "Off" End If End Sub Public Sub mlfpUpdateReset() mlvpAutomatic = False mlvpTime = 0 ThisWorkbook.Worksheets(mlchSheet) _ .Cells(mlchAuto_Y, mlchAuto_X).Value = "Auto is off" ThisWorkbook.Worksheets(mlchSheet).Shapes(mlchButton) _ .OLEFormat.Object.Caption = "On" End Sub

The constants on the top of the module are defining the names of the objects in the sheet and the positions of the relevant cells. The time interval is defined by the constant "mlchInterval" and the variable "mlvpAutomatic" holds the current state; namely, whether the automatic update is active or not. And the variable "mlvpTime" stores the time for the next update.

The function "mlfpUpdate()" checks whether the update is active. If not, then the function activates the automatic update and let the Excel timer function "Ontime()" call my function "mlfhTimer()" after the defined interval. If the update is already active, the function "mlfpUpdate()" will deactivate the automatic updates. Additionally, the function changes the caption of the button in each case. The function "mlfpUpdateReset()" is only used for resetting the automatic update and called each time the workbook is opened.

Let's have a look to the function "mlfhTimer()". Firstly, a reference to a "FillFormat" object of the rectangle is created by using the "Fill" property. Then, it is possible to update the used picture by calling the method "UserPicture()" and setting the URL as argument value.

The screenshot above shows the effect by using a sample URL. The last thing I had to do, was to link the function "mlfpUpdate()" to the form control button. Please note, that when you are testing the code from the sample file, you may enter an appropriate URL to the cell E5. No validation is done in the code.

• Sample file for this article

This article has also been published on the German Version of the Excel Ticker blog.