Excel Camera Tool

[CBrine]

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

[CBrine]

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

[Dave]

Hi CBrine again :) U can fix your clipboard crashes with a bit of code. HTH. Dave
https://www.mrexcel.com/board/threads/vba-code-to-merge-workbooks-quits-unexpectedly.1075671/#post-5167334

[CBrine]

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.

[Dave]

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

[snb]

It's a very well known phenomenon.

It won't occur if you use:


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

[Artik]

If the images in the Dashboard are dynamic images (they are linked to ranges), I would modify the proposal snb to this form:
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:
'Exit For
If the images are to be static, do not specify a second parameter when calling PastePic.

Artik

[CBrine]

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