Application.Workbooks not identifying all open workbooks

[Herodotus07]

Hi all,
First post here, I read the rules, lemme know if I missed something.

I have a macro that I wrote for a buddy a few years ago to automate pulling user submitted football picks into a master file. It worked seamlessly for the past two years. As of this morning, the macro no longer loops through all open workbooks. In the past, the macro would loop through all open workbooks, read-only or read/write, and would remove restricted editing prior to pulling the data. Now, it pulls data from one open spreadsheet, and stops.

I did some testing, and a key problem seems to be that now (using Excel 2016) the Application.Workbooks.Count value only shows the master spreadsheet, and none of the other open workbooks.

This is the section from my main code:

Public wksht As Worksheet, wkbk As Workbook, cl As Range
Public scoresheet As Workbook, i As Integer, usersheet As Worksheet
Public namearray() As String, selecteduser As String
Public weeksheet As Worksheet, R_thurs As Range, R_sat As Range, R_sun As Range, R_mon As Range
Public Rd_thurs As Range, Rd_sat As Range, Rd_sun As Range, Rd_mon As Range
Public tl As Range, br As Range, sheetsowner As Range
Public lastsecurity As Long




'shortcut ctrl+shft+t
Sub TransferPicks()


Set scoresheet = ActiveWorkbook


If InStr(1, scoresheet.Name, "Scoresheet") = 0 Then
    MsgBox ("You are starting on the wrong sheet. Start on the scoresheet. That, or you do not have ""Scoresheet"" in your spreadsheet title.")
    Exit Sub
End If


i = 0


For Each wksht In scoresheet.Worksheets
    If wksht.Name <> "Master Sheet" And wksht.Name <> "Results-Graph" And wksht.Name <> "WGP" And wksht.Name <> "Scoresheet" And wksht.Name <> "Results" And wksht.Name <> "Analysis" Then i = i + 1
Next wksht


ReDim namearray(1 To i)


i = 1


For Each wksht In scoresheet.Worksheets
    If wksht.Name <> "Master Sheet" And wksht.Name <> "Results-Graph" And wksht.Name <> "WGP" And wksht.Name <> "Scoresheet" And wksht.Name <> "Results" And wksht.Name <> "Analysis" Then
        namearray(i) = wksht.Name
        i = i + 1
    End If
Next wksht




With Application
    If .ProtectedViewWindows.Count > 0 Then
        For i = 1 To .ProtectedViewWindows.Count
            .ProtectedViewWindows(1).Edit
        Next i
    End If
End With

And this was the small test I ran to confirm that it is the Application.Workbooks at fault:

Sub wkshts()


Dim wbs As Workbook


For Each wbs In Application.Workbooks
    MsgBox (wbs.Name)
Next wbs


MsgBox (Application.Workbooks.Count)


End Sub

In the test, the workbook.name only displays the master workbook, and the application.workbooks.count only shows 1, though there are 4 total workbooks open.

My first assumption is that an update to Microsoft Excel 2016 (and we run 365 for Business) has caused a loss of visibility to other open workbooks, but I don't know how to fix this. I searched for other posts on this, but found nothing specific to my problem. Any help from the hive mind?

Thanks,
Karl

 

[Herodotus07]

Additional note, with the master sheet open (read/write) and 3 protected workbooks open, if I change the test code to report

Application.ProtectedViewWindows.Count

, it only reports 1, not 3.

 

[RoryA]

Does it work if you do this:

With Application
    Do while .ProtectedViewWindows.Count > 0
            .ProtectedViewWindows(1).Edit
    Loop
End With

?

 

[Herodotus07]

Thanks for the suggestion. Gave it a few tries and nope. Trying this code, it didn't identify any .ProtectedViewWindows, even though I had three open. It's odd, with much of the code I've tried, sometimes it will find 1 of 3 open workbooks, and other times it will find 0. Haven't been able to determine what I'm doing (file opening order, etc) that could be causing this.

 

[RoryA]

Have you checked the processes in Task Manager to make sure you're not running different instances of Excel?

 

[Herodotus07]

It does show four total instances of Excel. I believe this is standard now with 2016. Is there a means by which I can force all workbooks to open in a single instance? Or should I search for topics on looping through instances of Excel? Thanks for the help.

 

[RoryA]

It's not the standard. Excel 2016 appears to run as separate applications but does not actually do so by default.

How exactly are you opening the files?

 

[Herodotus07]

Thanks for all the help RoryA, based on your musing, I tried a few different ways, to varying results:

1. Open all files, one at a time, from the email - Result, four instances of excel
2. Open all files at once, from the email - Result, four instances of excel
3. Open all files, one at a time, from a directory on my desktop - result, one instance of exel (this would likely function).
4. Open all files at once, from a directory on my desktop - result, four instances of excel.
5. For kicks and giggles, opened four different (not related at all to this project) files, all .xlsx, no macros, from Desktop at once - result, four instances of excel.
6. Open a file (.xlsm, not trusted) after four instances of excel (.xlsx) exist - result, adds book to the first instance of excel.

Scratching my head a bit on this one. Starting to think I should just change this to have the user save all files to a directory, then write a Dir loop to open all files, one at a time, from that directory. That may fix this. But I'd like to know what is going on that would cause Excel to create new instances when it shouldn't need to, or wasn't told to do so.

 

[Herodotus07]

One last interesting tid bit, this macro just stopped working this morning. It was working yesterday, no problems. Could a system update to Office 365 for Business cause issues?

 

[RoryA]

Could a system update to Office 365 for Business cause issues?

Absolutely. Happens frequently I'm afraid with the new subscription model.