Excel Camera Tool

I've created a business solution and used the camera tool to reflect some data from about 12 separate spreadsheets. All the "snaphots" exist on a single "Dashboard".
The solution worked great for about 2 months then started crashing and had massive slow downs. After a day of digging into the issue, it appears the camera tool creates a "Copy" of the picture over and over again(At save? or at change of data?, not sure when). So my sheet was crashing because it had 3000+ images(lots of people accessing the tool) embedded in it. I used some vba code to erase the pictures, and the crashing slow downs disappeared.

My post is two fold...
1. To let people know this behavior occurs.
2. To hopefully find someone who has encountered this and found a solution because the camera is the only solution I've found to do what I need.

Thanks
Calvin

PS- Been a while since I posted last....years maybe. So hello to anyone that recognizes me still :)

Hi CBrine again :) U can fix your clipboard crashes with a bit of code. HTH. Dave
https://www.mrexcel.com/board/thread.../#post-5167334

Dave, Thanks for the reply, but it's not associated to the clipboard. I'm using a built in excel tool called Camera. It allows to to display an excel range in real time in another area in excel automatically. The crashing is a result of the number of Pics it takes. It seems to work by adding a new screenshot of the range automatically, but doesn't erase the old one. Not sure when the update occurs, but I suspect it does it everytime a save occurs. Since 10+ people access the file, plus autosave, it ends up creating 1000s of the same image for some reason. That then causes the slow downs and crashes.

I'm not familiar with the camera tool but U can code to create a picture of any range and then display it wherever, remove it and replace it as needed... simulated real time. But it does use the clipboard for which the previous link would be useful. Maybe others have some familiarity with using the camera tool and will be able to help. Good luck and stay safe. Dave

It's a very well known phenomenon.

It won't occur if you use:

Code:

---

Sub M_snb()
    Sheet1.Range("A1:A17").CopyPicture
    M_snb_paste Sheet1.Cells(4, 4)
    Sheet1.Range("G1:G17").CopyPicture
    M_snb_paste Sheet1.Cells(3, 9)
End Sub

Sub M_snb_paste(c00)
    For Each it In Sheet1.Shapes
      If it.TopLeftCell = c00 Then
          it.Delete
          Exit For
      End If
    Next
   
  Sheet1.Paste c00
End Sub

---

If the images in the Dashboard are dynamic images (they are linked to ranges), I would modify the proposal snb to this form:

Code:

---

Sub RestoreSnaphots()
  With Worksheets("Sheet1").Range("A1:A5")
    .CopyPicture
    Call PastePic(Worksheets("Dashboard").Range("B2"), .Address(External:=True))
  End With
 
  With Worksheets("Sheet2").Range("D10:G15")
    .CopyPicture
    Call PastePic(Worksheets("Dashboard").Range("E2"), .Address(External:=True))
  End With
End Sub


Sub PastePic(rng As Range, Optional Formula As String = "")
  Dim Shp As Shape
  Dim wks As Worksheet
  Dim wksActv As Worksheet
 
  Set wksActv = ActiveSheet
 
  Application.ScreenUpdating = False
 
  Set wks = rng.Parent
  wks.Activate
 
  For Each Shp In wks.Shapes
    If Shp.TopLeftCell.Address = rng.Address Then
      Shp.Delete
      'Exit For
    End If
  Next Shp
 
  wks.Paste rng
 
  If Len(Formula) > 0 Then
    wks.Shapes(wks.Shapes.Count).DrawingObject.Formula = Formula
  End If
 
  ActiveCell.Activate
 
  wksActv.Activate
End Sub

---

You should find a moment when you start the RestoreSnapshots procedure. Perhaps the good time is to open the workbook and maybe activate the Dashboard. You have to decide for yourself when to use the right event.
Once you're sure the Dashboard contains unique images, you can uncomment the line:

Code:

---

      'Exit For

---

If the images are to be static, do not specify a second parameter when calling PastePic.

Artik

I was hoping for a non vba solution, but it's looking like that may not be possible. The coding is food for thought, and I can leverage that for my own vba solution.
I'm almost thinking that it might be easier to delete the extra pictures when the spreadsheet opened.
Thanks for the responses!

Cal