Running Macro Causes Reference File to Save somewhere else

%3CLINGO-SUB%20id%3D%22lingo-sub-2098209%22%20slang%3D%22en-US%22%3ERunning%20Macro%20Causes%20Reference%20File%20to%20Save%20somewhere%20else%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2098209%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can't%20seem%20to%20replicate%20this%20error%20but%20it%20happens%20at%20least%20once%20a%20week.%20I%20open%20a%20file%20where%20I%20created%20some%20script.%20Does%20not%20matter%20if%20it%20is%20an%20older%20file%20or%20a%20newer%20one%20and%20I%20get%20the%20dreaded%20error%20%22Can't%20find%20Project%20or%20Library%22.%20This%20means%20that%20the%20Reference%20Library%20is%20missing%20a%20Module%2FTool%20that%20it%20can%20no%20longer%20reference.%20I%20scratch%20my%20head%20and%20wonder%20for%20a%20moment%2C%20%22I%20did%20not%20make%20any%20changes%20to%20my%20tool%20references%20so%20how%20can%20this%20be.%20To%20further%20agitate%20me%20When%20I%20open%20VBA%20Editor%20and%20select%20Tool%20Reference%20the%20sheet%20does%20nothing.%20Won't%20show%20me%20tools%20and%20now%20the%20code%20has%20disappeared.%20I%20can%20see%20the%20modules%20in%20the%20project%20window%20but%20the%20sheet%20is%20blank.%3C%2FP%3E%3CP%3ESo%20first%20I%20have%20to%20fix%20it.%20Usually%2C%20I%20can%20fix%20it%20by%20first%20closing%20out%20of%20all%20Excel%20Apps.%20Then%2C%20while%20holding%20down%20Ctrl%20and%20clicking%20on%20a%20new%20Excel%20app%20to%20open%2C%20I%20open%20Excel%20in%20Safe%20mode.%20I%20open%20the%20file.%20Then%20I%20go%20into%20VBA%20Editor%20and%20select%20Tools%20Reference%20and%20I%20can%20see%20that%20one%20of%20my%20files%20instead%20of%20being%20stored%20in%20with%20the%20system%20files%20it%20is%20normally%20saved%20to%2C%20it%20has%20been%20saved%20to%20my%20C%20Drive%20Documents.%20When%20I%20go%20look%20there%20the%20file%20is%20not%20there.%20However%2C%20if%20I%20search%20for%20the%20file%20in%20my%20system%20files%20(C%3AProgram%20Files%5CCommon%20Files%5CMicrosoft%20Shared%5CVBA)%20I%20find%20it%20and%20re-sync%20it.%3C%2FP%3E%3CP%3EBut%20what%20is%20crazy%20to%20me%20is%20how%20did%20that%20file%20location%20change%20in%20the%20first%20place%3F%20Has%20anyone%20else%20experienced%20this%20issue%20more%20than%20a%20few%20times%3F%3C%2FP%3E%3CP%3EI%20am%20100%25%20certain%20it%20is%20nothing%20in%20my%20code%20causing%20this%20to%20occur.%3C%2FP%3E%3CP%3EI%20think%20the%20problem%20relates%20to%20when%20Excel%20has%20a%20large%20file%20or%20several%20files%20open%20causing%20it%20to%20run%20at%20a%20high%20PC%20Utilization%20rate%2C%20Excel%20begins%20to%20act%20very%20crazy.%20When%20this%20occurs%20and%20I%20start%20shutting%20things%20down%2C%20the%20next%20time%20I%20re-open%20the%20file%20is%20when%20I%20will%20notice%20the%20file%20reference%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2098209%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

I can't seem to replicate this error but it happens at least once a week. I open a file where I created some script. Does not matter if it is an older file or a newer one and I get the dreaded error "Can't find Project or Library". This means that the Reference Library is missing a Module/Tool that it can no longer reference. I scratch my head and wonder for a moment, "I did not make any changes to my tool references so how can this be. To further agitate me When I open VBA Editor and select Tool Reference the sheet does nothing. Won't show me tools and now the code has disappeared. I can see the modules in the project window but the sheet is blank.

So first I have to fix it. Usually, I can fix it by first closing out of all Excel Apps. Then, while holding down Ctrl and clicking on a new Excel app to open, I open Excel in Safe mode. I open the file. Then I go into VBA Editor and select Tools Reference and I can see that one of my files instead of being stored in with the system files it is normally saved to, it has been saved to my C Drive Documents. When I go look there the file is not there. However, if I search for the file in my system files (C:Program Files\Common Files\Microsoft Shared\VBA) I find it and re-sync it.

But what is crazy to me is how did that file location change in the first place? Has anyone else experienced this issue more than a few times?

I am 100% certain it is nothing in my code causing this to occur.

I think the problem relates to when Excel has a large file or several files open causing it to run at a high PC Utilization rate, Excel begins to act very crazy. When this occurs and I start shutting things down, the next time I re-open the file is when I will notice the file reference error.

0 Replies