Protected View Now Preventing Macros From Copying, Editing, or even Identifying.

[Jeffrey Mahoney]

Ok, Excel has changed again. The snippet of a macro below has worked for years, even for workbooks that are in protected view. Now it doesn't see them at all. As soon as I click Enable Editing, the macro works.

I'm really hoping this isn't permanent.

Does anybody know of a workaround?

Sub FindWB()
  Dim WB As Workbook
  Dim v As Variant
  
  For Each WB In Application.Workbooks
    Debug.Print WB.Name
    
    If InStr(WB.Name, "Cash Flow Export") > 0 Or InStr(WB.Name, "Header Report") > 0 Then
      WB.Activate
      Range("A4").Select
      Set v = ActiveCell
      Exit For
    End If
  Next WB
End Sub

 

[DanteAmor]

Do you have any error messages?
Or what is it that no longer works?

Try the following without selecting the cell.
Make sure you have a workbook open with one of the names ("Cash Flow Export" or "Header Report"), in the same Excel application where you are running the macro.

Sub FindWB()
  Dim WB As Workbook
  Dim v As Variant
  
  For Each WB In Application.Workbooks
    Debug.Print WB.Name
    
    If InStr(WB.Name, "Cash Flow Export") > 0 Or InStr(WB.Name, "Header Report") > 0 Then
      Set v = WB.Sheets(1).Range("A4")
      Exit For
    End If
  Next WB
End Sub

 

[Jeffrey Mahoney]

Hey Dante,

I debugged the macro. In the FOR loop (For Each WB In Application.Workbooks) it never sees the open workbook containing the report until I press the "Enable Editing" button. This has changed. Usually it would see the report workbook and I could get the macro to interact with it; copy data, alter the cells, and so on.

The report opens into the only instance of Excel that I already have running.

Jeff

 

[Jeffrey Mahoney]

Dante,

I've been meaning to "Really" learn Spanish. Not just high school from 40 plus years ago. I could watch your Youtube videos.

 

[DanteAmor]

I've been meaning to "Really" learn Spanish. Not just high school from 40 plus years ago. I could watch your Youtube videos.

It's never too late, but you can watch the videos with English subtitles.

it never sees the open workbook containing the report until I press the "Enable Editing" button

If the workbook has protected view, you cannot see its information from VBA, it is as if it were not open, but you can try to edit the workbook from the same code, for example:

Sub FindWB()
  Dim WB As Object
  Dim v As Variant
  Dim sName As String
  
  For Each WB In Application.ProtectedViewWindows
    sName = WB.SourceName
    If InStr(sName, "Cash Flow Export") > 0 Or InStr(sName, "Header Report") > 0 Then
      WB.Edit
      Set v = Workbooks(sName).Sheets(1).Range("A4")
      Exit For
    End If
  Next WB
End Sub

 

[Jeffrey Mahoney]

I think I'll just add the code below to take away the Protected view status and then run my normal code. Thank you

'Remove protected view status
  For Each WB In Application.ProtectedViewWindows
    WB.Edit
  Next WB