Consulting

Results 1 to 8 of 8

Thread: Excel Camera Tool

  1. #1
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location

    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
    The most difficult errors to resolve are the one's you know you didn't make.


  2. #2
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    PS- Been a while since I posted last....years maybe. So hello to anyone that recognizes me still
    The most difficult errors to resolve are the one's you know you didn't make.


  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    Hi CBrine again U can fix your clipboard crashes with a bit of code. HTH. Dave
    https://www.mrexcel.com/board/thread.../#post-5167334

  4. #4
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    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.
    The most difficult errors to resolve are the one's you know you didn't make.


  5. #5
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    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

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  7. #7
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    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

  8. #8
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    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
    The most difficult errors to resolve are the one's you know you didn't make.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •