Tag Archives: image

excel to image

Excel to Image – VBA Save Range or Workbook as Image

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

How to save an Excel Range as an Image file (JPG)? Today’s post is inspired by my readers emails. In some of my previous emails I considered other Excel Image scenarios such as Adding Images to Excel using VBA or using the Excel Camera Tool to create an image snapshot in Excel. However today I stumbled across a different scenario: Saving an Excel Range as an Image File.

Excel to Image: Save Excel Range as Image

Excel Range to Image
Excel Range to Image
Let us start with an example. The easiest way to create an image in Excel is to Select an Excel Range, next hit the Copy button from Home ribbon and finally by clicking Paste with Picture.

Seems at first like we are just one step away from saving the image as an image file right?

Excel: Right click only to find... there is no Save As Image
Excel: Right click only to find… there is no Save As Image
Wrong! Only problem being… there is no Save As Image button easily available in Excel!

Of course you might say – why no copy and paste to MS Paint or another Image Editor? Yes that is always an option. But let me show you a much better and dedicated tool for taking Snapshots in any Windows application. Next I will show a way to achieve the task above with a simple VBA Macro.

Snipping Tool

Windows Snipping ToolOne way is to use the almighty Microsoft Snipping Tool which is great for creating image snapshots and saving them as images (PNG files).

Windows Snipping Tool Example
Windows Snipping Tool Example
You can see how the Snipping Tool works easily.

The problem however is that the Snipping Tool is not very precise and often detailed Excel Range images are cumbersome to achieve. This is where as usually we can use a bit of Visual Basic for Applications to automate this task…

Excel to Image with VBA

Now let us create an Image from an Excel Range like a pro. First copy the Excel VBA code below to an existing or new VBA Module:

What does the VBA Macro do? The Sub will do the following

  1. Copy the selected Excel Range and paste it as a Picture

  2. Copy the Picture to an empty Chart

  3. Ask for the destination file name

  4. Save the Chart as a JPG image

Now you may as Why not use VBA to save the pasted Picture as an Image file?. Well, you can’t (not so straight forward at least). The workaround above works pretty well however.

Using the Excel to Image VBA

run excel to image macro
Select Excel Range and open Macros
First select the Excel Range you want to save as an Image. To run the VBA Macro click the Macros button to open the Macros window.

run the excel to image macro 2
Select the VBA Macro and click Run
All that is left is to select the VBA Macro from the Macro window and to hit Run.
excel to image save window
Lastly name your destination file and hit Save
There may a short pause for the macro to process the image, shortly after the Save As file dialog should appear. Simply select your destination file name and hit Save and that is it!

Save Excel as Static Image Workbook

As usual I am leaving the best for last. Say you want to share an Excel Workbook as readonly. You can try protecting the Password Protecting your Excel documents but the safety is limited if you want to protect your underlying formulas.

excel to image vba macro
Save your Excel Workbook as a Static Image Workbook
What better way to protect your formulas then to send an Excel Workbook with print screen images of each and every Worksheet? The VBA Macro will additionally save the copy of your file as an XLSX file which means all VBA Macros will be removed as well.

Excel to Static Image Workbook VBA

Below the VBA code snippet to create a copy of your Workbook where every Worksheet is an image copy of the original.

Great right? I have noticed that in most cases the Image Workbook might however be a bit larger than the original file which has its slight downside. Then again this will depend heavily on the type of Workbook (more formatting larger file size).

Let me know what you think!

excel vba camera tool

Excel Camera Tool – create an Image snapshot in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (3 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
excel pictograph featured

Excel Pictograph – Charts with Pictures

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

Today something much lighter than usual, more to do with presenting data than just crunching itHow to Create an Excel Pictograph? Or, in other simpler words, How to Create an Excel Chart with Images!

Images are Powerful Stuff! Our Brain processes Images 60 Thousand Times Faster than Text! For us Consultants, Marketers and Office Clerks this is important to understand, although often neglected. Hence the term Death by PowerPoint, known also as Death By Slideas in overwhelming your audience with an abundance of text and information squeezed into a single slide.

If you want to get Your Message across use Pictures instead. Today I will show you a simple, but effective Excel Trick you can use to make the most out of your Data Visualizations – to transform Dull Charts into Impressive Infographics.

How to Create an Excel Pictograph?

example excel pictographLet’s start with the Basics. An Excel Pictograph is a Chart that replaces standard Bars, Pies and other Graphic Elements with Pictures. Creating Excel Pictographs is incredibly easy, hence making it an incredibly Powerful Excel Tip / Trick.

Create an Excel Chart

To create an Excel Pictograph we need to have a Chart to work on first. If you are new to Excel Charts feel free to listen to Chandoo’s Excel Chart Tutorial here. I created a simple Chart below:

Simple Boring Excel Chart
Simple Boring Excel Chart

Find an Image Icon for the Chart

The Chart above is Boring. Isn’t it? Just Raw Data. It takes a moment to take it in. Now let’s find an interesting image to replace the Boring Barchart! Here are some great resources I use to find Image Icons:

  1. IconFinder a great database of Free and Premium Image Icons. I really appreciate the large amount of free images they have you don’t need to link back to

  2. Icon Archive a clipart image database

  3. Icon Finder yet another icon database

Be sure to find an Icon that will suitably visual the data you are trying to exemplify.

Paste the Image to your Excel Spreadsheet

past image to excelNow copy your image to your Excel Worksheet. To do that go to the INSERT Ribbon Excel tab. Select Pictures if you have the Image on your workstation, or Online Pictures if you need to download the Image from the Web. Follow the steps and hit Insert.

Copy & Paste the Image onto your Chart

This is the important part:
past image to excel chart

  1. Copy the Image by clicking on it and hitting CTRL+C

  2. Select the Data Visual select your Charts Bars, Pie slices or any other Data Visual Element which you want to replace with an Image

  3. Paste the Image by hitting CTRL+V

Configure the Image

The Chart is starting to look good. But often stretching the Image distorts it. We need to replace the Stretch with either the Stack or Stack and Scale with options:
excel pictograph configure image settings

  1. Go to Data Series options by right clicking on the Bars and clicking Format Data Series...

  2. Go to Fill Options and select Stretch, Stack or Stack and Stretch with. That last options requires you specify the Units/Pictures ration. In our case 1000$ should equal to 1 picture, so I input 1000

Too make the Image look even better, you may also want to go to SERIES OPTIONS and adjust the Gap Width!
The Final Effect looks more like this:
final excel pictograph

vba image

Add an Image from VBA in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
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.

Go to this post to learn how to Save Excel Range as Image (JPG) using 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)