Can't remove a link with pop up each time I close a file

[anasttin]

Hi all,

I inherited a file that has a weird link that I can't seem to remove.

Each time I close the file, it has a popup which says something like:

"Cannot find F:\winfcd\NGBEBUD.xla[ngbebud'!autocloseauto_c, which has been assigned to run each time dummy.xlsb is closed. Continuing could cause errors. Cancel closing?"

This is what I've tried to remove the problem...

-inserted a blank tab and deleted ALL other tabs.
-viewed the "This workbook" in VBA to see if there was any autoclose code
-viewed all vba modules for any code and there is none
-done a search on each tab for links

None of this has helped and the file is also far bigger than it should be like there are hidden tabs, but there aren't.

I'm happy to upload the stripped out file, but don't know how to attach it to this post.

Thanks for any help.

[dflak]

One more thing to check: Named Ranges.

Here is how to attach a file

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[anasttin]

Hi all,

I checked for named ranges and it didn't help.

I've now attached the file.

Thanks for any help.

[dflak]

I opened the workbook and I closed it and I did get the message. What's even more amusing is that I saved it as a non-macro enabled file XLSX and I still get the message. I didn't think XLSX files could run macros!

I also agree that it's about one MB bigger than it should be.

[dflak]

I have a new error message. I changed the file to a ZIP extension and opened it and looked at the XML code (not that I know much about XML) and I removed all the external links I could find. I renamed it with an XLSB extension and I got an error message stating that it was repairing the file. When I closed it I got a new message as shown in the picture. Progress of a sort I guess. I'll take a look at the external links instead of deleting them next time.

[dflak]

P.S. This leads me to believe that the file was damaged at the XML level. I am almost certain that I can't figure out how to fix that, but I'll see what I can find when I poke around tomorrow.

[dflak]

If you change the extension on the file to ZIP, you can download the archive and look at the XML code behind it.

In the folder xl, there is a sub-folder called externalLinks.

In this folder, there are a number of bin files. I am not sure what these are or how to open them.

Also in this folder is a sub-folder called _rels

In this folder are XML files that correspond to the bin files in the parent directory.

File #13 looks like the text file that is attached.

Which is our "smoking gun."

If you play with the XML code in these directories you can recompress the archive, slap an XSLB extension on it and launch Excel again.

I tried playing with file 13. I deleted both the bin and rels files and Excel crashes. I tried replacing both files with a copy of another file and Excel crashes.

I deleted all the external links and the file got a lot smaller but still crashes with the error message shown in the previous post.

I now know where the error message is coming from. I don't know why it causes the crash or how to fix it.

FYI: these "links" don't show up even with a VB scan for links.

[dflak]

OK, I don't know what I might have destroyed in the process but this is what I did.

I made a compressed version of the workbook (New Dummy.zip). I also created a completely new workbook called Clean.xlsb. I made sure this workbook has two sheets since the original workbook has two sheets. I compressed this workbook (Clean.zip)

I extracted both workbooks to their appropriate directories. New Dummy and Clean. I navigated the xl folders on both directories. I copied / pasted the worksheets folder from New Dummy to Clean replacing the directory that was already there.

I went back up to the root level of the Clean Folder. I compressed the contents into a new ZIP file. I changed the name of this zip file to "New.xlsb." I launched the file and got some error messages to repair the file. One of the things lost was the print settings. I saved the file and closed it. It closed OK. It now opens OK.

The file now is also down to a reasonable size. I don't know what else got lost. Clearly any VB code might have gotten lost, but that can be "transferred." I don't know about table definitions and named ranges. I bet they are gone too. I imagine if I looked for these pieces, I could copy them over. I do think I got the pages and formulas. I noticed that I lost the sheet name and formatting.

On second thought it just might be worth while to write a VB cloning program: create a blank workbook, go through each sheet on the bad workbook, copy paste the whole sheet into the new workbook. Hopefully the "infection" is in the workbook and not the worksheets.

[anasttin]

Dflak... you are amazing.

I super appreciate all the effort you put in to try and find the problem. It is a weird one for sure.

Thanks so much again.

Have a great day.