title: Turn off Protected View in Excel VBA
body: <p>I need to extract data from a file ('DatabaseExport.xlsx') that has been generated and newly opened from an intranet site. The file opens in Protected View (yellow banner at top of screen).  I think it's necessary to disable the protected view so that I can extract data from the file.  My VBA is contained in a separate file 'GetAndAnalyseData.xlsm': </p>
  
  <pre><code>NameOfNewFile = "ooo"
  Do
      On Error Resume Next
          Application.ActiveProtectedViewWindow.Edit  'This never works first time
          NameOfNewFile = Left(ActiveWorkbook.Name, 14)
      On Error GoTo 0
  
      If NameOfNewFile = "ooo" Then
          ttt = MsgBox("this should not be possible!", vbOKCancel)
          If ttt = vbCancel Then Stop
      End If
  Loop While NameOfNewFile = "ooo"
  </code></pre>
  
  <p>When I run the code:</p>
  
  <ol>
  <li>I get a message box saying "this should not be possible!"</li>
  <li>If I click OK, I keep getting the same messagebox, and the file remains in Protected View. </li>
  <li>If I click Cancel and then F5 (to continue execution) the loop works as it should (protected view is cancelled, the value of TEMP is set to the filename, the loop exits and the macro continues. </li>
  </ol>
  
  <p>The line where I try to assign a value to NameOfNewFile returns an error if I don't use 'Resume Next':</p>
  
  <blockquote>
    <p>Run-time error '91': Object variable or With block variable not set</p>
  </blockquote>
  
  <p>I have tried several methods to fix this: </p>
  
  <ol>
  <li><p>Running this line before downloading the file, and also immediately before attempting to turn off Protected View, but still the behaviour is the same.</p>
  
  <pre><code>Application.AutomationSecurity = msoAutomationSecurityForceDisable
  </code></pre></li>
  <li><p>Making the folder (where the downloaded file is stored) into a trusted location, but this is not allowed by my user settings.  </p></li>
  <li><p>Unchecking the "Enable Protected View for files originating from the Internet" box in Trust Center, but with this box unchecked I can't open the file.</p></li>
  <li><p>Inserting this code (but the "Set wbPV line" causes Run-time error '424': Object Required): </p>
  
  <pre><code>Dim wbPV As Workbook
  If Application.ProtectedViewWindows.Count &gt; 0 Then
      Set wbPV = ActiveProtectedViewWindow.Edit
  End If
  </code></pre></li>
  </ol>
  
  <p>How can I turn off Protected View programmatically for this type of downloaded file?  Or is there some other way to get data out of the file?</p>