Hi.

I have a program that has been running without a hitch since Excel 2007. A user signed up to Office 365 got upgraded to Office 2016. Now the program fails when looking for a specific open workbook. Here is the code where the failure occurs:

Public Function EmailData() As EmailDat

Dim wboWB As Workbook
Dim i As Integer, iNameCol As Integer, iAddrCol As Integer, iSendEmailCol As Integer
Dim EmDat As EmailDat

Set EmDat.EmailAddressList = Nothing
Set EmDat.SendEmail = Nothing

'find the CT People List sheet--must be currently open.  If not found the function returns the email addresses dictionary object set to Nothing.
For Each wboWB In Workbooks
    If Left(wboWB.Name, 18) = "People List Export" And EmDat.EmailAddressList Is Nothing Then
        'we found the first occurance of the People List. If dic is not Nothing then we already found it and want to ignore all other occurances
        
        Set EmDat.EmailAddressList = CreateObject("scripting.dictionary")
        Set EmDat.SendEmail = CreateObject("scripting.dictionary")
        
        With wboWB.Worksheets("Export")
            'Set the column indexes for the name, email address and email flag ("Mail Generated Timesheet?") columns
            iNameCol = Application.WorksheetFunction.Match("Full Name", .Range("A1:AA1"), 0) - 1 'the person's name is used as the dictionary key
            iAddrCol = Application.WorksheetFunction.Match("Email Address", .Range("A1:AA1"), 0) - 1
            iSendEmailCol = Application.WorksheetFunction.Match("Mail Generated Timesheets?", .Range("A1:AA1"), 0) - 1
            
            'Step through the CT data recording the name as the key, the address and send-email flag for each person in the file
            For i = 1 To .UsedRange.Rows.Count - 1 'minus 1 to account for the column heading row.
                EmDat.EmailAddressList.Add .Range("a1").Offset(i, iNameCol).Value, .Range("a1").Offset(i, iAddrCol).Value
                EmDat.SendEmail.Add .Range("a1").Offset(i, iNameCol).Value, .Range("a1").Offset(i, iSendEmailCol).Value
'                Debug.Print .Range("a1").Offset(i, iNameCol).Value & ": " & EmDat.EmailAddressList.Item(.Range("a1").Offset(i, iNameCol).Value) & " : " & EmDat.SendEmail.Item(.Range("a1").Offset(i, iNameCol).Value)
            Next i
        End With
    End If
Next wboWB

EmailData = EmDat

End Function
The line 
For Each wboWB In Workbooks
only returns a single open workbook, usually the workbook containing the code. It doesn't seem to matter how many workbooks are open, the workbooks collection only returns a single workbook. Since the workbook being looked for isn't found, the routine always returns a null object reference even though the desired workbooks is open. I tried putting "Application." before "Workbooks" in the hope that it would more specifically qualify the collection...to no avail.

Has anyone seen this issue and, if so, was any solution found. In the research I've done so far, it appears 2016 opens new instances of Excel with each open workbook. I'm guessing they have a bug in the code that only sees open workbooks in the current instance of Excel, which is why the collection only contains a reference to the workbook containing the code.

Hope someone can help.

Peter