Protected View

[JerryH09]

Dear All,
I occasionally get data files scraped from the web. They come in protected view and it is easy to manually enable editing so I can use the data. I would like to use those few manual keystrokes in a macro. The recorded code is:
ActiveWindow.ActivateNext
Application.ActiveProtectedViewWindow.Edit = False
ActiveWindow.ActivateNext


The ActivateNext lines are because the macro is where I want to use the data not the data files themselves but when I run it I get runtime error 91 object variable not set. I can dim Application but I've got no idea how to set it. On error resume next just ignores the application line. Any help would be much appreciated.
Regards,
JerryH09

 

[GR00007]

Activate the workbook that's open, if the protection had already been removed you would get an error, this checks to see if the protection is active and sets to false:

Windows(vWorkbookName).Activate
If Application.ProtectedViewWindows.Count > 0 Then
    Application.ActiveProtectedViewWindow.Edit
End If

 

[JerryH09]

Hi GR00007 thank you so much for getting back to me. When I plug your code into mine I get:
Sub test6()
ActiveWindow.ActivateNext
If Application.ProtectedViewWindows.Count > 0 Then
Application.ActiveProtectedViewWindow.Edit
End If
ActiveWindow.ActivateNext
End Sub

I get a runtime 91 error
i'm trying to unprotectview from a different spreadsheet. If you could see a way round this I'd be very grateful
Regards,
JerryH09

 

[GR00007]

Did you try debug to determine which line is causing the error?
If you step through the code you can watch which window is active.

 

[JerryH09]

Hi GR0007,

 

[JerryH09]

Hi GR00007, It's the fourth line of code starting application. Excel seems to want this defined.
regards,
JerryH09

 

[GR00007]

I'm sorry, the If condition works for me to bypass the ActiveProtectedViewWindow.Edit.
Is this book being opened after being emailed?
Check out https://www.mrexcel.com/forum/excel...-error-91-when-opened-another-computer-3.html

 

[JerryH09]

Hi GR00007 Your code works great when applied to the scraped file but it's my use of ActiveWindow.ActivateNext that's causing the problem. Have you any idea how to activate a workbook without calling it by name? I would like to activate workbookname in your code but the name changes daily so I don't know the name when I'm trying to get data from it.(eg Worksheets(2).activate) If you can't so be it but your help has been very much appreciated anyway

 

[GR00007]

Are you trying to open the most recent file in a directory? Is the file from email? The source of the file determines the method of trying to get it.
I have a routine for grabbing the most recent file in a directory, but at a loss email delivery scraping.

 

[JerryH09]

Hi GR0007,
I have spent weeks trying to sort this out and pissed off one of your fellow board members in the process (no names mule999) but thanks to your great bit of coding it is all coming together. I had a lightbulb moment last night and thought if I can't access the protected view file from my macro why can't I access my macro from the protected view file AND IT WORKS. I can't show you experts anything about Excel but I intend showing my gratitude by spending the next six months helping others on the MrExcel platform.
People like you make this work.
Thanks again
Jerry Hodgkinson
PS If you've already received this then apologies
PPS I've already helped one person who didn't know how to use Application.screenupdating = false