0

I have a number of Excel files that refresh themselves via macro, and a master file to control opening the other. The master file has an Auto_Open macro that opens each doc, runs the macro in the doc, then closes it. Each doc's macro essentially refreshes all the queries inside and saves the doc in two different places. To top it all of, I am using Window's Task Scheduler to open the master doc every two hours, kicking off the whole process.

This generally works pretty well. However, I will occasionally get an error for one the docs that says "Cannot find project or library." This occurs seemingly at random and with a different doc each time (though never the master doc). Once this error appears, Excel will crash completely every time I try to open the VBA window. The macro in said doc will no longer run via the master file's Auto_Open macro, and I have to recreate said doc from scratch.

I have tried to find the library as suggested by Microsoft (https://learn.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/can-t-find-project-or-library) but to no avail. Opening the VBA window in the affected file causes an immediate crash, and following the steps in the article above for an unaffected file reveals nothing wrong. The crash returns this error text:

Problem signature:

Problem Event Name: APPCRASH

Application Name: EXCEL.EXE

Application Version: 16.0.11231.20130

Application Timestamp: 5c518be9

Fault Module Name: VBE7.DLL

Fault Module Version: 0.0.0.0

Fault Module Timestamp: 5c064824

Exception Code: c0000005

Exception Offset: 00000000000b555a

OS Version: 6.3.9600.2.0.0.272.7

Locale ID: 1033

Code in Master File:

Sub Auto_Open()

Application.Wait (Now + TimeValue("0:00:10"))

Application.Calculation = xlCalculationManual

Workbooks.Open ("C:\Users\aowens\Desktop\Queries\ATSReports\ATSReports.xlsm")
Application.Run "'C:\Users\aowens\Desktop\Queries\ATSReports\ATSReports.xlsm'!Macro"
Workbooks("ATSReports.xlsm").Close False

Workbooks.Open ("C:\Users\aowens\Desktop\Queries\MiscLookups\MiscLookups.xlsm")
Application.Run "'C:\Users\aowens\Desktop\Queries\MiscLookups\MiscLookups.xlsm'!Macro"
Workbooks("MiscLookups.xlsm").Close False

(this pattern repeats for 5 other files)

Sample macro within one the files:

Sub Macro()

Dim errorcount
Dim broken
Dim this As Date

this = now()
errorcount = 0

On Error Resume Next

ThisWorkbook.Connections("Query - MasterROCL").Refresh
If Err <> 0 Then
errorcount = errorcount + 1
broken = broken & " ROCL"
End If
Err = 0

ThisWorkbook.Connections("Query - MasterRMEL").Refresh
If Err <> 0 Then
errorcount = errorcount + 1
broken = broken & " RMEL"
End If
Err = 0

ThisWorkbook.Connections("Query - MasterRHIL").Refresh
If Err <> 0 Then
errorcount = errorcount + 1
broken = broken & " RHIL"
End If
Err = 0

ThisWorkbook.Connections("Query - MasterREXH").Refresh
If Err <> 0 Then
errorcount = errorcount + 1
broken = broken & " REXH"
End If
Err = 0

Calculate

ThisWorkbook.Save
Application.DisplayAlerts = False
ThisWorkbook.SaveAs ("R:\Operations\Dashboards\Queries\ATSReports.xlsm")

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.to = "aowens@explorenetwork.org"
.Subject = errorcount & " Errors for " & Format(now(), "MM/DD HH:MM") & " ATS Refresh"
.htmlBody = " ~ " & Round(1440 * (TimeValue(now()) - TimeValue(this)), 0) & " mins. Broken:" & broken
.Send
End With

End Sub
TylerH
  • 20,799
  • 66
  • 75
  • 101
A. Owens
  • 1
  • 1
  • You seem to be closing the files that you open but do you ever close the `master` file? Is the `master` file still open when your scheduler attempts to open another instance of it? – Zac Feb 14 '19 at 16:58
  • 1
    Also, **if** the connections are allowed to refresh in the background, I can see lots of things all trying to happen at once here.. – CLR Feb 14 '19 at 17:02
  • You're not clearing the `Err` object if you encounter errors you're ignoring. TBH, that combination of `Application.Run`, `On Error Resume Next`, and `Auto_Open()` looks like a disaster waiting to happen even ***before*** you throw Window's Task Scheduler into the mix. What is the purpose of all of this Workbook churn? I can't imagine that there isn't a better way to approach this. – Comintern Feb 14 '19 at 17:13
  • BTW, your error is an access violation - one of your pointers got dropped on an apartment boundary somewhere. This has little to do with the VBA code other than how it relates to trying to do a bajillion things at once with something that is inherently single threaded. – Comintern Feb 14 '19 at 17:15
  • https://stackoverflow.com/questions/54559743/excel-vba-cant-find-project-or-library/60075328#60075328 scroll to Greg Dobbs answer - this works. – Alan Marsh Aug 10 '20 at 15:34

1 Answers1

1

I often run into the same error as you under similar circumstances. I can't tell you what causes the error or how to stop it but i can help with this

"and I have to recreate said doc from scratch."

To recover the file you need to open the file in Excel's safe mode (hold ctrl and open Excel to activate Safe Mode) then open VBE > Debug > Compile Project. Then save and close, next time you open the file it should be fine.

UpsideDownCutlery
  • 11
  • 2