Workbook.Open silently fails to function, execution continues - Totally baffled

[BCVolkert]

I am working on an Excel workbook (CollectorWorkbook) that is to open a series of about 5000 workbooks (DataWorkbooks) and collect data from specific cells in those DataWorkbooks. To do this, I want to use a UDF in about 30 cells of the CollectorWorkbook.

I have used a similar approach to gather data into a similarly constructed CollectorWorkbookForWord data to gather information from a series of Word documents and it works well there. In that case a UDF in CollectorWorkbookForWord successfully opens the WordDocument if it is closed when the UDF is called and retrieves text from about 85 fields in the WordDocument. Subsequent calls to the UDF use the same instance of the open WordDocument and steps through about 2000 of the documents and takes about 9 seconds per document.

In the CollectorWorkbook, the call to the UDF is =ExcelCellText(RangeName,Full_File_Name). The UDF is to use a currently open instance of Full_File_Name if it is available and open Full_File_Name if it is not. This works when Full_File_Name is open but does not work if Full_File_Name is closed. No error message is produced. To be clear, ExcelCellText passes Full_File_Name to a call to GetExcelWorkbook (code follows) within the VBA for the UDF ExcelCellText.

I have stepped through the applicable code beginning with ExcelCellText, through the various internal procedures and back to the calling cell in CollectorWorkbook using F8 and did not observe anything unexpected except in the few lines of GetExcelWorkbook following the comment including "BIG TROUBLE HERE". Debug.Print statements throughout produce expected results with the exception of the sequence preceded by "BIG TROUBLE HERE" and results that we'd expect after a failure to open the DataWorkbook.

I did a test on a simplified version of the call to Workbooks.Open that demonstrated to me that something I do not understand is happening. See the WonkyTest code near the end of this post.

I'm suspicious that there is something about my code, computing environment, or work habit that is producing something that would make a decent Twilight Zone episode on a programming network. I've been working on this issue a few days and have had a similar problem on and off for several years in other contexts. Short of reinstalling Windows, I'm open to suggestions because I've run out of ideas.

Relevant properties of my world are as follows:

• Windows10Pro validated and fully updated.
• Microsoft 365 installed locally, validated and fully up to date.
• During the last week or so of trying to solve this problem I have done Quick Repair, Online Repair, Uninstall/Reinstall M365
• I am the Administrator.
• I have completed various Shutdown, Restart cycles ad nauseum.
• My Internet is working most of the time.
• The CollectorWorkbook and DataWorkbooks are on OneDrive.
• I get the same behavior when OneDrive is closed as when it is open.
• The VBA is in my Personal.xlsb file in the XLSTART folder.
• VBE Options, General: Break on Unhandled Errors, Compile on Demand, and Background Compile are selected.
• The User, OneDrive, and XLSTART folders are trusted locations.
• In the Trust Center, Macros are Enabled and VBA Projects are trusted.
• I have Cleaned the code using MZ-Tools.
• After exporting all the modules in Personal.xlsb, I deleted Personal.xlsb, created a new one using the Macro Recorder and then imported modules.
• I have a lot of modules (about 68) in my Personal.xlsb. I have looked everywhere for functions that may have similar names (none found).
• Option Explicit is in effect.
• I get the same behavior using Late and Early binding when using this code.
• I've stepped through the code using F8 and did not find anything unexpected except for the problems following "BIG TROUBLE HERE".
• Hard coded version works (see WonkyTest) below.

Public Function GetExcelWorkbook(ByVal Fullname As String, _
   Optional ByVal ReadOnly As Boolean = False, _
   Optional ByVal Visible As Boolean = True, _
   Optional ByVal CreateAppInstanceIfClosed As Boolean = True, _
   Optional ByRef ExcelWorkbookWasOpen As Boolean, _
   Optional ByRef ExcelWasRunning As Boolean) As Excel.Workbook ' Object ' Excel.Workbook
   ' ----------------------------------------------------------------
   ' Procedure Name: GetExcelWorkbook
   ' Purpose: Returns an Excel Workbook object sa warranted.
   ' Procedure Kind: Function
   ' Procedure Access: Public
   ' Parameter Fullname (String): The fully qualified path to the workbook.
   ' Parameter ReadOnly (Boolean): ReadOnly property for a closed workbook.
   ' Parameter Visible (Boolean): Visibility for a closed workbook.
   ' Parameter CreateAppInstanceIfClosed (Boolean): Determines if Excel will be opened if it is closed.
   ' Parameter ExcelWorkbookWasOpen (Boolean): Returned value is True if Fullname was already open.
   ' Parameter ExcelWasRunning (Boolean): Returned value is True if Excel was already open.
   ' Return Type: Workbook
   ' Author: Bruce Volkert
   ' Date: 8/24/2020
   ' ----------------------------------------------------------------

   ' Enhanced from: _
   https://stackoverflow.com/questions/9373082/detect-whether-Excel-ExcelWorkbook-is-already-open

#If LateBinding Then
   Dim appExcel As Object
   Dim oEWkb As Object
#Else
   Dim appExcel As Excel.Application
   Dim oEWkb As Excel.Workbook
#End If

   Const szsource As String = "GetExcelWorkbook"
   Dim bDebugging As Boolean
   bDebugging = True ' If this routine is working set bDebugging = False; otherwize True

   Dim bReporting As Boolean
   bReporting = bDebugging
   If bReporting Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & "Starting"

   Dim sFullName As String
   Dim sFile As String
   
   Set GetExcelWorkbook = Nothing
   sFullName = Trim(Fullname)
   sFile = Dir(sFullName) ' This works.
   ExcelWasRunning = False
   ExcelWorkbookWasOpen = False
   
   If bReporting Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & "sFullName = " & sFullName
   If bReporting Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & "sFile = " & sFile

   ' Returns value of ExcelWasRunning. This works 8/24.
   Set appExcel = GetExcelApp(CreateAppInstanceIfClosed:=CreateAppInstanceIfClosed, Visible:=Visible, ExcelWasRunning:=ExcelWasRunning)
   
   If bReporting Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & "ExcelWasRunning = " & CStr(ExcelWasRunning)
   
   If appExcel Is Nothing Then
      ' There is nothing to do because the Caller did not want Excel to be open if previously closed.
   Else
   
      On Error Resume Next
      Set oEWkb = appExcel.Workbooks(sFile) ' This works when sFile is open. As intended, oEWkb is Nothing if sFile is not already open.
      On Error GoTo 0

      If oEWkb Is Nothing Then
      
         ExcelWorkbookWasOpen = False
         If bDebugging Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & sFile & " was not open already."
         
         On Error GoTo GetExcelWorkbook_Error
         
         ' BIG TROUBLE HERE. F8 steps through this If/Then/Else sequence without hesitation; however, NO action is apparent. The workbook does not open. No error message is produced.
         If FileExists(sFullName) Then ' As expected, this DOES confirm that sFullName exists. 8/25
            ' Set oEWkb = appExcel.Workbooks.Open(FileName:=sFullName, ReadOnly:=ReadOnly) ' This does not work. With valid sFullname oEWkb remains Nothing 8/24 PM
            Set oEWkb = appExcel.Workbooks.Open(FileName:=Fullname, ReadOnly:=ReadOnly) ' This does not work. With valid sFullname oEWkb remains Nothing 8/25 PM
         Else
            ' As expected, the following warning does not appear for valid sFullname and Fullname.
            MsgBox "The requested file " & sFullName & " does not exist.", vbOKOnly Or vbCritical Or vbSystemModal, Application.Name
         End If
         If bDebugging Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & sFile & " should be open."

      Else
      
         ExcelWorkbookWasOpen = True
         If bDebugging Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & sFile & " was open already."

      End If
      
   End If

Finally:
   ' TODO:  Delete call to WonkyTest when issues are resolved.
   Call WonkyTest ' This produces the same erroneous behavior. The workbook does not open.

   Set GetExcelWorkbook = oEWkb
   If bDebugging Then Debug.Print VBA.format(Now(), "yyyy-MM-dd hh:mm:ss") & "  " & m_szModule & "." & szsource & ":  " & "C - Excel should be running"

   Set oEWkb = Nothing
   Set appExcel = Nothing
   
   Exit Function

GetExcelWorkbook_Error:
   ' The following message does not appear for valid sFullname or Fullname. However, the workbook does not open. It is not hidden, and does not show up as a VBE Project.
   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetExcelWorkbook, line " & Erl & "."
   '   On Error GoTo GetExcelWorkbook_Error
   GoTo Finally

End Function

Just to check my syntax and to know if I've been asking Excel and VBA to do something that is valid, I created the test procedures below in the same standard Module. Everything worked as intended (and still does) when I stepped through it using F8 directly and through the function call. But when I included:
Call WonkyTest
in the GetExcelWorkbook function (after Finally), it did not open the workbook correctly.

Public Sub WonkyTest()
   ' When I step through this using F8 directly. the workbook opens correctly.
   Dim sFullnameTyped As String
   sFullnameTyped = "C:\Users\MyUserName\OneDrive\Documents\MAVA\MealPlans\A legitimate file name.xlsm"
   Workbooks.Open (sFullnameTyped)
End Sub

Sub WonkyTestCallDirectly()
   ' When I step through this using F8, the Call to WonkyText causes the workbook to open correctly.
   Call WonkyTest
End Sub

Sub WonkyTestCallThroughFunction()
   Dim worked As Boolean
   worked = WonkyTextWorked()
   Debug.Print "WonkyTextWorked = " & worked
   ' When I step through this, the value of worked is True.
End Sub

Function WonkyTextWorked() As Boolean
   Dim oWkb As Excel.Workbook
   Call WonkyTest
   On Error Resume Next
   Set oWkb = Workbooks("A legitimate file name.xlsm")
   On Error GoTo 0
   If oWkb Is Nothing Then
      WonkyTextWorked = False
   Else
      WonkyTextWorked = True
   End If
   Debug.Print "WonkyTextWorked = " & WonkyTextWorked
End Function

 

[RoryA]

A UDF can't call Workbooks.Open on the same application instance as the container workbook (thankfully).

 

[BCVolkert]

Thanks Rory,

So, if my CollectorWorkbook (the container?) needs to open a DataWorkbook, then I need to open the DataWorkbook (in the same instance of Excel) with a subroutine call ahead of the UDF. If the DataWorkbook of interest is not open, the calls to the UDF in the CollectorWorkbook would just need to return an error message or null entry. Is this a correct understanding?

The code that drives the collection will change an index that is used to select the filename from an Excel Table in the CollectorWorkbook. I guess I could use some sort of Worksheet_Change event focused on the index to trigger the opening of the next DataWorkbook of interest or simply return the DataWorkbook as an object if it is already open . Once open, the existing ExcelCellText UDF would work as it currently does, the data could be collected into an Excel Table in the CollectorWorkbook, the DataWorkbook closed, and the index incremented or otherwise changed to restart the collection on the next DataWorkbook. In this manner, even manual changes to the index would trigger the procedure invoking Workbooks.Open for a closed workbook and just return appropriate values for a DataWorkbook that happened to be open.

Is there a cleaner or more elegant approach I should be looking into?

P.S. A few years ago you pointed me to using the Shell approach to opening a new instance of Excel while I was working on a similar thing being driven from Word. It was good advice then and I'm still following it. This collection is being driven from Excel. My UDFs were checking for an existing instance of Excel and using it; but, I suppose there is a good reason for not allowing a UDF to open a workbook. It sure seems reasonable for the UDF to be able to open a Workbook that is closed. Although I accept that the limitation exists, I don't understand why that would be the case.

Anyway, thanks again. Your response was crazy quick. I really appreciate that. Hopefully you are COVID free and can stay that way.

Bruce

 

[RoryA]

I think that to answer that properly, I'd need to understand exactly why you need to use this approach rather than say formula links to the other workbooks, or just a macro that is run as and when necessary, or queries, or some other method.

And to clarify, a UDF can open another workbook, it just needs to use a separate instance of Excel, which doesn't sound like it would be much of an issue for your purposes.

As far as COVID goes, so far so good for now, but we are being very careful! Trust you are too.

 

[BCVolkert]

Rory,

Thanks for your interest. I'll try to explain; but, brevity is not my strength.

My wife used a spreadsheet template to create a lot of individual spreadsheets (about 5000 of them during her career). We are gathering the data to see if we can spot some trends and/or judge effectiveness of calculations carried out in these spreadsheets affected outcomes.

I'm using this particular CollectorWorkbook to run through all these old spreadsheets and collect information from the cells in each sheet into an Excel Table. Each row of the table contains the information from about 30 values. Some cells contain text (e.g. a Name), other cells contain dates or numbers. These sheets all have named ranges in them. The later versions have a few more ranges (e.g. a few parameters) that were not in the early versions. The UDFs check for the existence of the ranges and gracefully return appropriate values.

I've collected information from Fields in Word documents and from plain text data using UDFs because it seemed to be a general way to proceed and potentially consistent for different types of source documents and desired return values (e.g. GetExcelCell(RangeName, FullFileName, TypeOfDataInDocument) seems a consistent with GetWordFieldText(FieldName, FullFileName, TypeOfDataInDocument), GetWordBookmarkDateTime(BookmarkName, FullFileName, TypeOfDataInDocument) and could be adapted to other sources of information as long as the general idea of a UDF to recover a particular value could be developed using the same sort of input parameters - the parameter, the file its in, and the type of information in the file represented by the parameter. I recognize that I'll need different UDFs for different types of data file; but, at least consistency of functionality would focus the effort. I also use different UDFs to return different types of result (e.g. the DateTime version reads text; but, returns an Excel data value.

The CollectorWorkbooks use a PowerQuery to import a list of files from .txt files created with a .bat file running a DIR command (old school). A macro in the my Personal.xlsb varies an index causing each file to be opened and the data collected. Keeping the macros in the Personal.xlsb makes managing the code easier and lessons learned solving one problem are available to the next problem. The data is frequently very messy. Information can be in the wrong field, poorly formatted information (e.g. weight in stones vs pounds, comments in fields where a date is expected, etc.). I'm cleaning this up with a combination of UDFs, string formulae, and just making changes to the messy source documents where that is the low-energy approach.

A nice characteristic of the UDF approach when I was collecting information from Word is that changing the index would result in a change to the FullFileName and the UDF would know that the Word document was not open and open it. Once opened, the other cells would populate from fields in the Word document without any drama. With this capability, I could collect the data from all the files by letting it run for a few hours, and then look at the messy data. Using filters tables in the CollectorWorkbook, I could see what the mess was like and correct the data in the source material by simply typing in the index of the offending document and edit the problem out of existence. Alternatively, I would write a string formula or create a special UDF to condition the data (e.g. change 65'' to 65", eliminating extra spaces and weird punctuation, correct entries for various friendly names to something consistent like Lastname, Firstname, etc.). The process of changing the index, making a simple change to the document, saving the document, and entering the next index could be done in about 20 seconds per document when I could remain focused and the change was trivial (e.g. adding a Bookmark name to a field, or removing trash from the field).

Looking at only the problem of scraping the data from the spreadsheets, I could probably just do formulae constructed using the FullFileName and RangeName; but, I'd still need to open the files. The UDF in the Excel/Word version would do that for me. In this Excel/Excel version, I'd need a macro to do the looping. To make changes to the source material, I work through the list of index numbers with problematic data. When the changes are complete, I re-run the whole collection process or just have VBA make updates to the collected data.

I'm experimenting with the Worksheet_Change thing now because I've done that sort of thing before. It's not clear to me how to open a new instance of Excel. I suppose its something like the code below based on your advice a few years ago. I guess I could force the creation of a new instance and then return the instance to the UDF; but, I'd need to pass the instance of Excel along to the other cells invoking the UDF or reopen Excel for each parameter that I'm collecting. Maybe that's not needed -- I clearly don't fully understand the mechanics of using a separate instance of Excel; even though I could create one. This is the code I use to get an instance of Excel for use in my procedures. Normally, I just get the current instance of Excel; but, if the procedure is invoked when Word needs something from Excel, it opens a new instance. A precursor of this UDF was particularly helpful when I needed to access Excel information to populate Content Controls in a Word document.

Public Function GetExcelApp(Optional ByVal CreateAppInstanceIfClosed = False, _
   Optional ByVal WaitTime As Long = 2, _
   Optional ByVal Visible As Boolean = False, _
   Optional ByRef ExcelWasRunning As Boolean) As Object ' Excel.Application
' ----------------------------------------------------------------
' Procedure Name: GetExcelApp
' Purpose: Returns an Excel Application object as warranted,
' Procedure Kind: Function
' Procedure Access: Public
' Parameter CreateAppInstanceIfClosed (Boolean): If True and Excel is not running, Excel will be started using the Shell command.
' Parameter WaitTime (Long): The time allowed for Excel to start. Default 2 seconds.
' Parameter Visible (Boolean): If True, a new instance of Excel will be visible. Existing instances are used if available. Visibility is unaffected.
' Parameter ExcelWasRunning (Boolean): Returned value is True if Excel was already open.
' Return Type: Object
' Author: Bruce Volkert
' Date: 8/24/2020
' ----------------------------------------------------------------

#If LateBinding Then
   Dim appExcel As Object
#Else
   Dim appExcel As Excel.Application
#End If

   On Error Resume Next
   Set appExcel = GetObject(, "Excel.Application")
   If Err Then
      ExcelWasRunning = False
      If CreateAppInstanceIfClosed Then
         ' Excel was not running
         Shell ("Excel.exe")
         WaitAFewSeconds WaitTime
         DoEvents
         Set appExcel = GetObject(, "Excel.Application")
         DoEvents
         appExcel.Visible = Visible
      Else
         ExcelWasRunning = True
         Set appExcel = Nothing
      End If
   End If
   On Error GoTo 0
   
   Set GetExcelApp = appExcel
   Set appExcel = Nothing
   
End Function

We are COVID free as far as we know. No cabin fever yet.

Bruce