27 thoughts on “Snapshot of an Excel Range”

1. You can also make a picture of a range by using the Camera tool. For some reason, the Camera tool is no longer on any of the toolbars, but you can add it yourself.

   Select View – Toolbars – Customize. In the Customize dialog, click the Commands tab. In the Categories list, click Tools. Then locate the Camera tool in the Commands list, and drag the icon to any toolbar.

   Using the Camera tool actually creates a “live snapshot” and it will reflect any changes made in the original range. To convert it into a “dead snapshot,” click the snapshot and then delete the formula from the formula bar.

   You can also create a live snapshot by pasting the copied range using the Edit – Paste Picture Link command (which is available only when you press Shift)

2. I’m not normally one to complain about Excel ’cause I just love it too much, but this small set of tools seems almost easter-eggish in its obscurity. Anyways, I was wondering if there are other features tucked away in this manner? I tried pressing Shift and clicking all the other pulldown menus but didn’t notice anything. (Maybe I should try Shft-Ctrl-Esc while singing the lyrics to Inagaddadavida backwards.)

   Doug

3. Sure is a great tip. I like the idea of turning the grid lines on and off.

   I use the camera tool all the time too so I can easily refer to info from other worksheets or worbooks, without the need to flick back and forth using my mouse or shortcut keys.

4. I second (or third or wherever we’ve got to) the Camera point. It’s pretty much the first customization I make when I get to a new install of Excel.

   I use it a lot to assemble complex panel-based reports: the individual “panels” can be built in their own worksheets and assembled on the output sheet as linked pictures.

   It’s been around since (at least) Excel 3.0, which was my first experience.

5. Doug: Shift File will give you a Close All option (at least in XL2000). I don’t know of any others, though.

   PS. There is an easter egg that involves singing Inagaddadavida, but nobody can stay awake through the instrumentals, so it’s never been found.

6. There are some other commands that behave differently when you have the Shift key pressed.

   Actually, here’s a recent post about that:

   http://www.mrexcel.com/board2/viewtopic.php?t=86516

7. I was playing with it 3 days ago! and I was amazed I found it… then when I was reading this blog today I found this tip. Weird ;)

8. Excel crashes each time I try to paste link after taking a snapshot with the camera. Maybe I should restart Excel after adding the camera to the toolbar.

9. Hi –

   I’ve been playing with the camera tool as well, but can’t seem to be able to access some sample VBA code by recording a macro whilst taking a snapshot. Whilst recording, I can’t place the snapshot anywhere.

   Any idea how I can either do this, or, alternatively, a pointer to the specific object model that snapshot uses so that I can play with this? It’d solve about a dozen snarky programming problems I’ve got!

   Thanks in advance,

   John

10. David –

    Are you trying to paste link the camera contents into another program? I was able to paste link into Word after copying with the camera tool, with results identical to having done a regular copy first. If you camera-copy something, in Excel there are no paste options available on the Edit menu, but just clicking anywhere parks the camera contents at the click.

    John –

    The macro recorder does not recognize the camera tool (been there, tried that). I think your best bet is to record yourself copying the old fashioned way, and using Paste Special – Link.

    – Jon

11. Hi Jon –

    Ahh, but I wanted that superspecial “Look! The Link Is Alive!” quality. Oh well, I guess I’ll have to start digging in the object model and see what I can dig up…

    Thanks!

    John

12. And just so that everyone can see how it works:

    Sub MakeALivingPicture()
    Windows(“Muster Industrie.xls”).Activate
    Range(“X25:x25?).Select
    Selection.Copy
    Range(“X41:x41?).Select
    ActiveSheet.Pictures.Paste(Link:=True).Select
    End Sub

    This simply takes selects a range (in this case a single cell, but it works for multiple cells…), copies it and places a linked picture into the second range.

    This is pretty neat for summarizing information from a whole slew of different sources…

    John

13. Camera Tool Problem

    I’ve taken a camera shot of a range that includes a chart, and placed the picture on another page. Whenever I try to print out/print preview the page with the linked picture, the original chart resizes itself. In typical Excel fashion, one chart gets squished into a horizontal line and another squishes vertically.

    Great blog – one of my regular reads.

    Thanks in advance,

    Rob S.

14. I’m experiencing the same problem with the camera tool.

    Snapshots of charts resize when printing and in print preview.

15. Hi
    Great Blog!!
    Yes I am experiencing the same issue with the camera tool that the last couple of posters have mentioned. I create a great looking worksheet using the camera tool, but then when I go to Print Preview or Print, the charts that the camera tool took pictures of become distorted. See this example:
    In worksheet: http://img.photobucket.com/albums/v410/martinperlin/Excel/before.jpg
    In Print Preview: http://img.photobucket.com/albums/v410/martinperlin/Excel/preview.jpg

    Appreciate any help or tips on this issue!

    Thanks,
    Martin

16. Every time we open and close the print preview a chart is resized until gets squized almost to the line.

17. I found the same. See microsoft Knowledge base item 211633 – XL2000: Linked Picture of Cells Changes Size with Print Quality (http://support.microsoft.com/kb/211633/en-us).

    My only workaround as been to remove the link before printing.

18. I found the camera tool utterly useless because of the graph resizing problem. The Microsoft knowledge base article doesn’t help much either, it shifts focus to fiddling with your printer’s resolution.

19. I wrote some VBA to remove – reattach the links based on the Workbook_BeforePrint, Workbook_BeforeSave and Worksheet_Deactivate events.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ‘Record all links and disable them
    DisableImageLinks
    End Sub

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ‘Make sure we are saving the image links
    EnableImageLinks
    End Sub

    ‘Enable links when deactivating the sheet with linked images
    ‘ASSUMPTION : links always point to different sheets
    Private Sub Worksheet_Deactivate()
    EnableImageLinks
    End Sub

    The corresponding VBA code :

    ‘Private variables to hold state image links
    Type ImageLink
    Sheet As String
    name As String
    Formula As String
    End Type

    Private ImageLinks() As ImageLink

    Sub DisableImageLinks()
    ‘ ************************************************************************************
    ‘ * This procedure removes all image links and puts them in an array. *
    ‘ * See : *
    ‘ * http://www.dailydoseofexcel.com/archives/2004/04/28/snapshot-of-an-excel-range/ *
    ‘ * http://support.microsoft.com/kb/211633/en-us *
    ‘ * Created : K. Pelckmans 12-Jan-2007. *
    ‘ * Version : 1.0 *
    ‘ ************************************************************************************
    Dim Pic As Picture, PictureCount As Integer, Sheet As Worksheet
    PictureCount = 0

    For Each Sheet In ActiveWorkbook.Worksheets
    For Each Pic In Sheet.Pictures
    If Len(Pic.Formula) > 0 Then
    PictureCount = PictureCount + 1
    End If
    Next
    Next

    If PictureCount > 0 Then
    ReDim ImageLinks(1 To PictureCount) As ImageLink

    PictureCount = 0

    For Each Sheet In ActiveWorkbook.Worksheets
    For Each Pic In Sheet.Pictures
    If Len(Pic.Formula) > 0 Then
    PictureCount = PictureCount + 1
    ImageLinks(PictureCount).Sheet = Sheet.name
    ImageLinks(PictureCount).name = Pic.name
    ImageLinks(PictureCount).Formula = Pic.Formula
    Pic.Formula = “”
    End If
    Next
    Next
    End If
    End Sub
    Sub EnableImageLinks()
    ‘ ************************************************************************************
    ‘ * This procedure relinks images to circumvent Excel bug. *
    ‘ * See : *
    ‘ * http://www.dailydoseofexcel.com/archives/2004/04/28/snapshot-of-an-excel-range/ *
    ‘ * http://support.microsoft.com/kb/211633/en-us *
    ‘ * Created : K. Pelckmans 12-Jan-2007. *
    ‘ * Version : 1.0 *
    ‘ ************************************************************************************
    On Error GoTo Handler
    Dim i As Integer
    For i = 1 To UBound(ImageLinks)
    Sheets(ImageLinks(i).Sheet).Pictures(ImageLinks(i).name).Formula = ImageLinks(i).Formula
    Next
    Handler:
    End Sub

20. To Kristof Pelckmans

    Kristof Pelckmans Says:
    January 22nd, 2007 at 2:48 am

    I wrote some VBA to remove – reattach the links based on the
    Workbook_BeforePrint, Workbook_BeforeSave and Worksheet_Deactivate events.

    Thank you very much for your contribution!
    You have solved for me the intriguing resizing problem when printing ‘camera links’.

    I am creating a complicated excel “apparel submission” workbook that includes capturing pictures of garments. People use “Insert… Picture… From File… ” to place pictures on a nominated area (landing pad).

    This landing pad has ‘camera shots’ taken from it and placed in various areas to reproduce information with out recreating the work.
    Alas, the printing becomes skewed when live ‘camera shots’ are within the print area.

    What you have provided is an instant solution (and worked with no errors first time) for printing ‘camera shots’.

    Again, thank you for providing a wonderful solution. It is much appreciated!

21. Hello to Kristof Pelckmans

    When I try to run the code, when it gets to Sub DisableImageLinks(), I get a Compile Error: User defined type not defined. The problem appears to be at:

    ReDim ImageLinks(1 To PictureCount) As ImageLink

    PictureCount = 0

    For Each Sheet In ActiveWorkbook.Worksheets
    For Each Pic In Sheet.Pictures
    If Len(Pic.Formula) > 0 Then
    PictureCount = PictureCount + 1
    ImageLinks(PictureCount).Sheet = Sheet.name
    ImageLinks(PictureCount).name = Pic.name
    ImageLinks(PictureCount).Formula = Pic.Formula
    Pic.Formula = “”
    End If
    Next
    Next

    End If
    End Sub
    Sub EnableImageLinks()
    ‘ ************************************************************************************
    ‘ * This procedure relinks images to circumvent Excel bug. *
    ‘ * See : *
    ‘ * http://www.dailydoseofexcel.com/archives/2004/04/28/snapshot-of-an-excel-range/ *
    ‘ * http://support.microsoft.com/kb/211633/en-us *
    ‘ * Created : K. Pelckmans 12-Jan-2007. *
    ‘ * Version : 1.0 *
    ‘ ************************************************************************************
    On Error GoTo Handler
    Dim i As Integer
    For i = 1 To UBound(ImageLinks)
    Sheets(ImageLinks(i).Sheet).Pictures(ImageLinks(i).name).Formula = ImageLinks(i).Formula
    Next
    Handler:
    End Sub

    Please could you help. Many thanks.

22. I have a solution for this problem now. The above will work but appears to have a bug in it. The better solution is to hold the data in a different s altogether. this problem can then be overcome.

    cheers
    Simon

23. I have a problem. When I open a work book contains photos or pictures, the workbook get opened but the pictures are not appearing. Can somebody please email me with the solution for this. Thanks. My email is kienlv@yahoo.com

24. Set the printer resolution to 1200dpi or something greater than the standard 200dpi.
    File>PageSetup>Page>Print Quality pull down
    After I did this, a Print Preview showed the image at the correct size.

25. I want to copy a chart from one worksheet to another as a picture so the picture will reflect any changes to the original chart. When I click on the chart, the camera tool is greyed out. If I hold down Shift and edit, copy picture, when I go to paste the chart on the other worksheet, press Shift, the past picture link is greyed out. Any suggestion on what I am doing wrong.

26. Pingback: Creating a VBA-free Excel dashboard « « The Excel Charts Blog The Excel Charts Blog
27. Pingback: Excel dashboards according to Charley "ExcelUser" Kyd

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.