When you embed another Excel file into an Excel 2007 or 2010 workbook, choosing only to show the icon for the file, you may find that after saving the Excel workbook that the icon text, picture or both text and picture is missing. In some cases the picture remains while the text is gone and in other cases only a blank white space is left, though it still works to open the embedded Office document when clicked. Here is what you would see:
In this case, the text is missing and the icon is blank.
This has been confirmed to happen when a third party program called Digital Guardian by Verdasys is installed on the system. The problem is known to happen with version 5.3.2 on the client and 5.3.1 on a server. Apparently this is a known issue that can be corrected by upgrading to version 6.1. Check with Verdasys if you are having trouble locating the update.
I am having this exact problem, but do not have Digital Guardian installed.
I wrote an add-in to prevent saving the main workbook if the embedded files are still open. Please note that this disables autosave and closes invisible workbooks. It works for multiple main workbooks opened, but it will fail once you save an embedded workbook inside of another embedded workbook – “inception”. Just a WIP and all comments welcome to better the code.
Add below in VBA Editor, and save as Excel Add-in – then add it in excel. Please also note that this currently only works with .xlsx extentions but easily changed. Lastly, this only works for embedding workbooks within workbooks one layer deep. Still goes crazy once you push the envelope.
Add to ThisWorkbook object:
‘create class object
Dim Action As New EventClass
‘run when excel starts. *Start listener
Sub Workbook_Open()
Application.AutoRecover.Enabled = False
Set Action.appevent = Application
MsgBox “Embed Add-in has started.”, vbOKOnly
End Sub
Add to Class Module and name it EventClass :
‘Once user clicks save, checks to see if the main workbook has objects that are open
‘note – room for improvement
‘this only works for ‘.xlsx’ files at the moment.
Public WithEvents appevent As Application
Private Sub appevent_WorkbookBeforeSave(ByVal wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each wb In Workbooks
If Not Windows(wb.Name).Visible Then
wb.Close
End If
Next
‘Checks if top level workbook has open objects.
‘If Not wb.Name = “Embed Add-in.xlam” Then ‘just in case the .xlam is picked up, do nothing.
For Each wb In Workbooks
If InStr(wb.Name, Replace(ActiveWorkbook.Name, “.xlsx”, “”)) And wb.Name ActiveWorkbook.Name Then
MsgBox “Close Embedded Workbooks :: ” & wb.Name, vbCritical
Cancel = True
Exit For
End If
‘MsgBox ActiveWorkbook.Name
‘MsgBox Replace(ActiveWorkbook.Name, “.xlsx”, “”)
Next
‘End If
End Sub