Can't open Excel XLSM file due to recent VBA Reference failing

[SixSigmaGuy]

This is really messed up. Please help me with a workaround that works. I'm completely disabled.
I have a workbook called Journal.xlsm and another workbook named TokenHandling.xlsm. TokenHandling.xlsm is stored on my OneDrive but I access it via my local OneDrive folder on my harddrive. Journal.xlsm's code accesses TokenHandling.xlsm and everything was working fine. Then someone on StackOverflow.com suggested that it would be more efficient if I just added a reference to TokenHandling.xlsm from Journal.xlsm, so I did that. Stupid me didn't make a backup like I usually do. I NEVER EVER go to OneDrive online because I access all my OneDrive files from my local copy. But now, when I try to open Journal.XLSM, it displays a message saying "Sorry, we couldn't find Sign in to your Microsoft account Libraries/TokenHandling.xlsm. Is it possible it was moved, renamed or deleted." But the file is right there. I can easily access it on my local OneDrive folder and I can access it by going online. Even if I put the path in the message into my browser, it opens the file just fine. Why isn't Journal.xlsm able to find it. More importantly, why on Earth is it trying to go to Sign in to your Microsoft account when I referenced the file on my Local harddrive, not online? Additionally, as soon as I close the messagebox, Journal.xlsm closes. So I have no way to remove the reference (that I know of). I can no longer open Journal.xlsm and I have to make daily updates in it.

 

[jkpieterse]

@SixSigmaGuy Isn't the problem in the other file you mention in your first post?

 

[SixSigmaGuy]

I'm afraid that this is getting to technical for me.
This might just be sending you down the proverbial rabbit warren but in case there VBA Project bin is relevant here is another link.
I was unable to find the VBA references just looking in the .bin with notepad though.

VBA Projects - Protection and vbaproject.bin - VBA and VB.Net Tutorials, Education and Programming Services

A VBA project is nothing but the bundle of macro(s) along with the host application in which it has been coded. For example, if we write a small reusable function to split a column of cells values into two using the MS Excel VBA Editor,

software-solutions-online.com

I tried the same thing with Notepad. I have since reproduced the bug that caused this problem and reported it to Microsoft. I'll try rolling back to a previous release of Excel. You certainly haven't led me astray, though.. Your suggestion of renaming the xlsm to zip was genius. It never occurred to me that such a simple trick could allow someone to dissect the XLSM file into separate components. I will try your new suggestions tomorrow. I'm in the Philippines and the day is winding down.

 

[Alex Blakenburg]

@SixSigmaGuy, Jan @jkpieterse is a well known expert in Excel, I suggest you take up his offer and share the Journal file with him.

 

[SixSigmaGuy]

@SixSigmaGuy, Jan @jkpieterse is a well known expert in Excel, I suggest you take up his offer and share the Journal file with him.

Absolutely, but I've received no such offer from Jan. @jkpieterse ,
the Journal.xlsm file is located low at: Journal..xlsm
The TokenHandler.xlsm file is located at: TokenHandling.xlsm
They are both on my OneDrive in the folder TempJourn. I gave you read/write access to the folder and the files. They are copies, so don't worry about damaging them.

I'm certainly willing to help you in any way I can in return if you ever need anything.

I'm pretty sure the problem is with Journal.xlsm. I can reproduce the issue reliably on my computer. Here are the repro steps I reported to Microsoft:

1. Create an XLSM file named REFERENCE.XLSM
2. Add a subroutine to the worksheet object. E.g.,
Sub Test()
Stop
End Sub
3. In the VBA Editor, rename the project (Tools->VBAProject Properties....->Project Name) to "Reference"
4. Save and close REFERENCE.XLSM
5. Create a second XLSM file named MASTER.XLSM (This is file that will contain the problem)
6. Repeat step 2 for this file, adding a subroutine
7. Repeat step 3 for this file, but name it "Master"
8 In the VBA Editor, add a reference (Tools->References...) to REFERENCE.XLSM
9. Save and close MASTER.XLSM
10. Reopen MASTER.XLSM after a few seconds (to make sure it had time to completely unload)

This is where I get the error message on my machine Everytime..

I'm running the preview version of Office 365, so if you don't get the error when opening Journal.XLM, can you please remove my reference to TokenHandler.XLSM, save it to my OneDrive Folder and let me know.

Thanks!!

 

[SixSigmaGuy]

Absolutely, but I've received no such offer from Jan. @jkpieterse ,
the Journal.xlsm file is located low at: Journal..xlsm
The TokenHandler.xlsm file is located at: TokenHandling.xlsm
They are both on my OneDrive in the folder TempJourn. I gave you read/write access to the folder and the files. They are copies, so don't worry about damaging them.

I'm certainly willing to help you in any way I can in return if you ever need anything.

I'm pretty sure the problem is with Journal.xlsm. I can reproduce the issue reliably on my computer. Here are the repro steps I reported to Microsoft:

1. Create an XLSM file named REFERENCE.XLSM
2. Add a subroutine to the worksheet object. E.g.,
Sub Test()
Stop
End Sub
3. In the VBA Editor, rename the project (Tools->VBAProject Properties....->Project Name) to "Reference"
4. Save and close REFERENCE.XLSM
5. Create a second XLSM file named MASTER.XLSM (This is file that will contain the problem)
6. Repeat step 2 for this file, adding a subroutine
7. Repeat step 3 for this file, but name it "Master"
8 In the VBA Editor, add a reference (Tools->References...) to REFERENCE.XLSM
9. Save and close MASTER.XLSM
10. Reopen MASTER.XLSM after a few seconds (to make sure it had time to completely unload)

This is where I get the error message on my machine Everytime..

I'm running the preview version of Office 365, so if you don't get the error when opening Journal.XLM, can you please remove my reference to TokenHandler.XLSM, save it to my OneDrive Folder and let me know.

Thanks!!

@jkpieterse , I left something really important out of my repro instructions. REFERENCE.XLSM needs to be saved in a local/synce'd OneDrive folder. If you don't have OneDrive, I don't think you'lll be able to repro the problem.

 

[jkpieterse]

I can repro the steps you mentioned. If I copy the file from my OneDrive sync folder to my desktop and open from there I can remove the reference without an issue.

I downloaded your Journal file, saved to my desktop. It has the "Mark of the web" (expected), which in this case might be beneficial. It opens in protected mode and if I click the Edit button it opens with the VBA project disabled (expected). Then it allows me to remove the reference, which is marked "MISSING" (expected). I unchecked that reference and uploaded it to my OneDrive: Journal..xlsm

 

[SixSigmaGuy]

I can repro the steps you mentioned. If I copy the file from my OneDrive sync folder to my desktop and open from there I can remove the reference without an issue.

I downloaded your Journal file, saved to my desktop. It has the "Mark of the web" (expected), which in this case might be beneficial. It opens in protected mode and if I click the Edit button it opens with the VBA project disabled (expected). Then it allows me to remove the reference, which is marked "MISSING" (expected). I unchecked that reference and uploaded it to my OneDrive: Journal..xlsm

It Worked!! Thank you so much.

Sorry for taking so long to reply, but I wanted to test it thoroughly before responding. One thing weird is that it still loaded TokehHandler.xlsm when Journal.xlsm was loading even though there was no longer a reference to it. I fixed that by creating a new workbook and copying all the worksheets and code to the new worksheet.

Thank you @jkpieterse and @Alex Blakenburg !!