#vba image

New Post has been published on http://analystcave.com/vba-create-image-from-vba-excel/

VBA: Create an Image from VBA in Excel

Images are what enriches our content, visualizing data enables us to compare results, notice patterns and provide insights. Most Excel files are bereft of any images, presenting raw data supported by boring charts. Dashboard often could use a couple of images here and there to visual a metric or trend. Today shortly on how to dynamically create, embed and delete images from your Excel Workbooks by using only VBA.

We will start by introducing the Shapes AddPicture function:

Shapes.AddPicture function

Syntax

Shapes.AddPicture( _ Filename, LinkToFile, SaveWithDocument, _ Left, Top, Width, Height)

Parameters

Filename File or URL of the picture to add.

LinkToFile Use MsoTrue or MsoCTrue by default. Full list here.

SaveWithDocument Use MsoTrue or MsoCTrue by default. Full list here.

Left Position (in points) of the upper-left corner of the picture relative to the left corner of the document.

Top Position (in points) of the upper-left corner of the picture relative to the top corner of the document.

Width Width of the picture in points.

Height Height of the picture in points.

Create an Image from VBA using a file

Let’s start with a simple example. Say we want to embed an image into our Excel Worksheet as such: To simply embed an image from a file we need to add a new Shape using the AddPicture function:

Call ActiveSheet.Shapes.AddPicture("C:\Users\User\Desktop\facebook.bmp", _ msoCTrue, msoCTrue, 0, 0, 100, 100)

This will add the image “facebook.bmp” to the upper-top corner of our spreadsheet. The size of the image will be 100×100 points.

Create an Image from VBA from an URL

Similarly we can create the image using an URL of the current weather conditions in the US:

Current weather in US: Image loaded from URL

Call ActiveSheet.Shapes.AddPicture( _ "https://i.imwx.com/images/maps/current/curwx_600x405.jpg", _ msoCTrue, _ msoCTrue, _ 0, 0, 600, 405)

This will load the image from the URL. Can take a couple of seconds to complete. Awesome right?

Now with this you can do cool things. Why not upload your project status/dashboard on a webpage and simply refresh it using VBA?

Deleting / Replacing images using VBA

Often you will want to replace an existing image or remove one. Not as obvious as it seems, you can’t replace an existing image – instead need to delete and recreate using a new picture.

Delete an image from VBA

Just deleting an image can be done like this – based on it’s index:

ActiveSheet.Shapes(1).Delete

Or like this if you need to obtain the Shape by name:

ActiveSheet.Shapes("Picture 1").Delete

Replacing images using VBA

Now let’s see what’s the correct way to replace an image (of similar sizes and location):

fileName = "C:\NewImage.bmp" 'Collect the location and size of the image Dim shHeight As Long, shWidth As Long, shTop As Long, shLeft As Long Dim s as Shape, ws as Worksheet Set ws = ActiveSheet Set s = ws.Shapes("Picture 1") shHeight = ws.Height: shWidth = ws.Width: shTop = ws.Top: shLeft = ws.Left 'Delete the image s.Delete 'Recreate the image using same location and size ws.Shapes.AddPicture(fileName, msoCTrue, msoCTrue, shLeft, shTop, shWidth, shHeight)

Related Post

VBA: VBA Class Tutorial, Custom Classes and Object...

Installation of Add-Ins

VBA: Scrape Google Search Results to CSV

VBA: CDate function

VBA: CBool function