Tag Archives: vba image

excel vba camera tool

Excel Camera Tool – create an Image snapshot in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Did you know you can use Excel to Image snapshots of your Excel spreadsheets ? Sure you can. What is more you can use this feature directly from VBA to achieve some impressive feats. Today we will learn how to use the Excel Camera Tool!

Excel Camera Tool

excel camera tool
Using the Excel Camera Tool
Let us start with exploring how to use the built-in Excel Camera Tool. The Excel Camera Tool is a button that let’s you create an image snapshot of any region of your Excel spreadsheet.

To use the Excel Camera Tool all you need to do is:

  1. Select an Excel Range

  2. Click on the Camera Tool Icon

  3. Click on any place in your Excel Worksheet

Add Excel Camera Tool Icon

Adding the Excel Camera Tool to your Quick Access Toolbar is easy. Just follow the steps below:

Select the Customize Quick Access Toolbar icon

Click on the arrow show the Quick Access Toolbar menu.
add excel camera tool
Next click More commands.

Browser Commands not in the toolbar

From the Choose commands from: menu select Commands not in the toolbar.
add excel camera tool
Now you should see an item called Camera in the list of Commands.

Add the Camera Tool

Now select Camera and hit the Add button to add the Camera tool to your Quick Access Toolbar.
add excel camera tool
Now you should see the Excel Camera Tool Icon in your Quick Access Toolbar:
excel camera tool.

Use Excel Camera Tool in VBA

The Camera Tool is very useful in your everyday work when you need to copy part of your Workbook as an Image i.e. send an image snapshot of an Excel Worksheet or Chart. Fortunately there is also an easy way to use it in VBA:

An example of how the macro works below:
excel camera vba
Nice huh? Well this is the beginning of things you can do. There are lot of cool ideas you can use this feature to:

  • Send image snapshot of Excel Worksheet via Email
  • Save Excel Workbook as static image – replace each Worksheet with a static Image to protect your formulas and calculations
vba image

Add an Image from VBA in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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.

Add 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:
image from vba
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.

Add an Image from VBA from an URL

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

Current weather in US: Image loaded from URL
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)