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.

 

[SixSigmaGuy]

I don't know why MrExcel replaced part of my URLs with "Sign in to your Microsoft account," but the URL was the URL to my OneDrive. Also wanted to add a few things: I've tried disabling macros, disabling OLE and ActiveX, and turning off my Addins and none of those work. If I open the workbook in protected view, though, I'm able to open it, but I can't copy any data or move worksheets to other workbooks. If I copy/paste the data to another workbook, it comes out all corrupted which I don' t understand. My last backup was on 2024-02-02, but I've written a ton of VBA code since then that I don' t want to lose. Why isn't there some way to open a workbook and instruct it to ignore references?

I've tried opening the workbook from another workbook using IDE code buy haven't found any methods that work for me. Maybe someone has a suggestion. I just want to open my workbook, remove the reference to my TokenHandling.xlsm file and save it back. I wish I had never added the reference.

 

[jkpieterse]

Did you remove the file from OneDrive? I get taken to an empty folder. Does it help to open the file with macros disabled?
You can change the way a link displays by using the Insert Link button above the post editor.

 

[SixSigmaGuy]

Did you remove the file from OneDrive? I get taken to an empty folder. Does it help to open the file with macros disabled?
You can change the way a link displays by using the Insert Link button above the post editor.

I did not remove the file and, yes, I disabled macros, activex, addins, with know luck. Thanks for the tip about links. I'll try that. In this case, the link was part of the error message, so I just posed it as is. I'll try editing it. Actually, I couldn't find a way to edit the original post. Is there a way?

 

[SixSigmaGuy]

"https://d.docs.live.net/1d5b46dcb0e31464/Code Libraries/TokenHandling.xlsm"
Above is the link I tried to include in my original post. I've enabled it so anyone with the link can view it

 

[Alex Blakenburg]

You can only edit a previous post within 10 mins of posting it.
A couple of other thing you can try:
Note: Please update your acccount profile to show what version of Excel you are running.
1) If you have MS 365 try opening your file in the Online version of Excel it may behave differently.
2) If you have no other links in your workbook you could change the file extension to Zip delete the externalLinks folder (in the xl folder).
then change the extension back

 

[SixSigmaGuy]

You can only edit a previous post within 10 mins of posting it.
A couple of other thing you can try:
Note: Please update your acccount profile to show what version of Excel you are running.
1) If you have MS 365 try opening your file in the Online version of Excel it may behave differently.
2) If you have no other links in your workbook you could change the file extension to Zip delete the externalLinks folder (in the xl folder).
then change the extension back

View attachment 107673

This isn't a link I added. it's reference that I added in the VBA editor under Tools->References. I can't even open the file to change anything. I can open it in Protected View, but I can't change anything. I'm hoping there's some way to open an Excel XLSM file and tell it to ignore all the references while opening.

After testing, I guess no one can access the above link I provided earlier unless they have my authentication info. Below is the link that allows anyone to view the file, but I don't see how that helps anyone solve my problem since that's not the same URL Excel is trying to access. Why on Earth can't Excel just use the UNC path I gave it when I added the reference.? OneDrive makes things so difficult sometimes.

"https://1drv.ms/x/s!AmQU47DcRlsdg_gdj8O_ecs-ZSEUGA?e=Hcoagt"

I am using Excel 365 Preview. I've already reported the problem to the Excel team.
I don't have any links in my file. I'll try your suggestion regarding externalinks

 

[SixSigmaGuy]

Well, your suggestion about using Online Excel sure provided me a lot of opportunities to try new things, but nothing has worked so far. As Far as I could tell, Online Excel has no feature t o do a file open to open a non-OneDrive file, so I uploaded the Journal.xlsm file to OneDrive. I was able to open it, but I can't access the VBA code or editor and, thus, can't remove the reference. I also tried doing a saveas to an ODS format, which I opened in my Desktop Excel, but that removed all my code modules and it no longer had a reference to my TokenHandler.xlsm file. So, still no way to fix the problem. About to try your external links suggestion

 

[SixSigmaGuy]

After renaming my xlsm to zip there was ExternalLinks folder. But your suggestion sure has opened a whole new playground of things to try. I've been trying to remove on component after another and renaming back to xlsm, but so far nothing has worked. Either it' still tries to find the reference, the vba code is completely gone, or it fails completely. Lot's of combinations to try.

 

[Alex Blakenburg]

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