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:
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.
This article has also been published on the German Version of the Excel Ticker blog.