Aug 16 2019 08:06 AM
I’ve spoken with Microsoft Excel customer support about this and there are no other known instances of the problem my team and I are having with two of our templates, which I designed myself from scratch about a year ago. They suggested I post this thread to a handful of forums, in hopes that some awesome software genius out there can help me out.
One crucial thing to note before I get into the detail of what’s happening is that we *only* encounter the issue once the file(s) are uploaded to SharePoint and accessed via a web browser (as opposed to opening from synced folder in Windows Explorer). In other words, if the file is saved locally on the machine or our internal server, the issue does not occur. Another thing to note is that it doesn’t *always* happen, and I haven’t been able to pinpoint the exact timing of when it actually takes place, since it’s an “invisible” problem. Which will make sense by the time you get to the end of this post. Anyways…
The two workbooks I designed each serve very different purposes, but are alike in the sense that they’re set up to automatically create a print-friendly, clean, professional looking report, the PDF form of which is the finished product that we send to our clients. These templates have about 20 separate tabs a piece, but we’re only going to focus on the three that are relevant to my problem. Those are:
1st Tab: Deal Inputs – This is where users enter/drop all the raw data for any given project, and set preferences for how things should calculate/display, etc. The sheet includes a bunch of stuff, but the problem I’m having only involves three ranges of merged cells. I’ve defined each of these separately as named ranges: ClientLogo, FrontCover, and BackCover. A different photo (jpg/png) gets inserted in each of the three named ranges, and sized to fill the entire range, horizontally and vertically. Moving on…
2nd Tab: Front Cover – This where the problem starts. It’s also page 1 of the final package we PDF and send to the client. When I first designed this template in Excel, after creating the Named Ranges listed above, I then inserted two new photos onto the Cover tab. I sized those appropriately to fit well with the rest of the auto-generated content included on the cover page (which also pulls from the Deal Inputs Sheet, among other places). After inserting, I selected the 4th photo just inserted, and in the formula bar (with the *photo* on the cover tab selected, I typed my reference to the first named range: =ClientLogo. I hit enter, and yay – my thumbnail client photo image that’s plugged into the Deal Inputs tab automatically takes the place of the original 4th image inserted, and is properly sized/positioned within the cover itself automatically.
I repeat the same process for the 5th photo I’ve inserted on the Cover tab, only for this one, I reference the 2nd named range: =FrontCover. Once again, the thumbnail from Deal inputs takes the original image’s place on the cover, and automatically resizes to fit the page.
3rd Tab: Back Cover – The problem continues here, and like the Front Cover, I followed the same process to initially set up the automatic back cover image here, using a 6th photo inserted on the Back Cover tab. This one references the third named range from Deal Inputs: =BackCover.
PROBLEM: Once the file is uploaded to/accessed from SharePoint via web browser by my colleagues and I, seemingly at random, I’ll notice that the file size has suddenly become massive (the first time I caught this, it was over 35mb, and usually is only ~6kb). Unless you’re paying attention to the file size, it’s impossible to see that there’s even a problem, which is that the “auto-pics” on the front and back cover tabs are duplicating themselves, seemingly at random, one on top of the other (so you can’t ever *see* there’s more than one image until you select the what appears to be the only one and drag to either side and reposition it, revealing a replica of it beneath where the one on top was moved from. If others on my team who are less Excel-savvy access the file multiple times daily over the span of a few weeks but it’s been a while since I’ve opened it myself, the images sometimes wind up having been duplicated literally THOUSANDS of times over. To fix it/clean up the file size, I just need to do a simple ‘Select All Objects’ and press delete, but my God if it’s not annoying AF.
The original/source photos that users insert onto the Deal Inputs tab are never duplicated. *MY* company logos are never duplicated on the Front Cover or Back Cover sheets (because those are also just regular photos dropped onto the worksheets, rather than photos auto-generated via reference to named range. It’s only those three ‘Result Photos’ contained on the covers - =ClientLogo, =FrontCover, and =BackCover. And again, it *only* happens in versions that are shared/saved to SharePoint.
Someone PLEASE help me figure out what is causing this. If you open the attachment, note that it only includes the three tabs I am referencing, so links/some named ranges/conditional formatting will be disabled.
Oct 28 2020 05:06 AM
I have exact same problem with linked pictures duplicating themselves.
This happens on files that are stored on OneDrive only.
Running Office 365 , 64 bit version
Nov 12 2021 07:56 AM - edited Nov 12 2021 07:57 AM
Have you already found the solution? I am having the same trouble as you and the document is getting very slow to operate
Dec 09 2021 08:27 AM
Happy to find this thread, have been having the same issue with one file exceeding 590MB.
The only resolution at this time appears to be to delete the duplicate objects. At least this can help to retain the original file.
Has anyone found the cause of this issue?
Feb 14 2022 06:26 PM
Mar 04 2022 09:32 AM
@kmnotorius 2022 and still having this problem. Files are saved to SharePoint. Duplication only happens when certain users access the file, so I'm assuming it's a problem with their computer, not all computers. Any linked photos or photos using the camera tool are duplicated each time the user accesses that file. If it is not caught quickly, the accumulation becomes so large that the file begins to work slowly. If it gets out of hand, the only way to fix it is to delete the tab because excel will lock up trying to select all objects and delete.
Mar 04 2022 09:58 AM
@kvpool I found that I could open the file in Excel 365 on the web browser and delete duplicate images. Trying to open some of the files in desktop application would result in lockup like you mention.
Mar 04 2022 10:02 AM
Mar 04 2022 10:57 AM
@kvpool ... Yup. Still happening on my end also, although, less frequently. I completely agree - it seems to only take place when *certain* users open the file, but even in cases where those users open, it doesn't always happen. As far as they've been able to recount, they're not doing anything differently at any given time, so I've still been unsuccessful in pin-pointing which key strokes/mouse clicks/operations are responsible for the issue. Very frustrating.
I do find that one of the "easier" fixes/ways to manage the problem before it gets out of hand is to open the selection pane from time to time, and look for duplicate names of photos. In the event it's happened again, it's much easier to select and delete the duplicates from the selection pane, rather than clicking on the duplicate photos themselves and deleting.
Mar 04 2022 11:00 AM
Mar 04 2022 11:08 AM
Jul 25 2022 02:58 PM
Jul 25 2022 02:58 PM
Sep 27 2022 05:11 AM
Has anyone found the issue I am facing the same problem and yes the file is on Onedrive/Sharepoint. is there any permanent solution to it?
Sep 28 2022 07:28 AM
Sep 28 2022 07:29 AM
Sep 28 2022 07:43 AM
Oct 20 2022 11:21 AM
Same problem using the camera tool. Images pile up until the spreadsheet slows to a crawl. The images are perfectly aligned so it's not readily apparent.
Apr 26 2023 03:23 PM
Apr 27 2023 02:25 AM
Hello,
I didn't find a reason for this. I draw a solution, though.
For my specific worksheet I listed in a table which images are "real" and coded a VBA macro that deletes any image not listed there and any duplicated image.
So the steps would be:
1. Tag your images
2. Create a table and list all of them
3. Create a sub and call this sub every worksheet.activate event
Code
Sub DeleteUnlistedAndDuplicateImages()
Dim ws As Worksheet
Dim img As Shape
Dim tbl As ListObject
Dim tblRange As Range
Dim nameList As Range
Dim imgName As String
Dim foundMatch As Boolean
Dim imgCount As Integer
'set worksheet to the active sheet
Set ws = ActiveSheet
'set table to the first table in the worksheet
Set tbl = ws.ListObjects("tblImages")
'set table range to include only the name column
Set tblRange = tbl.ListColumns("Image").DataBodyRange
'loop through all shapes in the worksheet
For Each img In ws.Shapes
'check if the shape is an image
If img.Type = msoPicture Then
'get the name of the image
imgName = img.Name
'set foundMatch flag to false
foundMatch = False
'loop through the name column in the table
For Each nameList In tblRange
'compare the image name with the name in the table
If imgName = nameList.Value Then
foundMatch = True
Exit For
End If
Next nameList
'if a match is not found, delete the image
If Not foundMatch Then
img.Delete
Else
'if a match is found, count the number of times the image appears in the worksheet
imgCount = 0
For Each img2 In ws.Shapes
If img2.Type = msoPicture And img2.Name = imgName Then
imgCount = imgCount + 1
End If
Next img2
'if the image appears more than once, delete all duplicates except for the first one
If imgCount > 1 Then
For i = 2 To imgCount
For Each img2 In ws.Shapes
If img2.Type = msoPicture And img2.Name = imgName Then
img2.Delete
Exit For
End If
Next img2
Next i
End If
End If
End If
Next img
End Sub