password protected excel in office 365

Copper Contributor

Hi Experts, 


I know that: 

"Excel for the web can’t open or edit workbooks protected by a password. In fact, passwords can’t be added, changed, removed, or recovered from Excel for the web. If you try to open a password protected workbook, you’ll see a message as it can't be possible and to Open it in Excel button to open the workbook in Excel desktop application". 


Now I have a problem. My company due to sever security legislation has blocked OneDrive. This means I can open one drive in web but can't download anything nor upload to one drive. Because of that my Excel (with password) is not usable. When I am opening it in OneDrive, its asking me to Open in Desktop Excel but I can't download to Desktop Excel because of security reason. So I am stuck.. 


Can anyone please help ?

15 Replies

@amitmahapatra 

The only option you have is to open the file in another Office package (no Excel for Web, best Excel 2007 to 2016) and unlock the file, see the instractions down unter please, if you need  to open a .xlsm file you need a VBA code.

If this is necessary, please contact me again.
You can do this as long as it is your workbook.
Attention: You may only use the following instructions for your own files. Otherwise you could be liable to prosecution.

Unprotect Excel sheet without password using 7-ZIP (free software)
Für diese Anleitung benötigen Sie das Programm 7-Zip, herunterladen können Sie dies in Internet: https://www.7-zip.org/download.html

Folgen Sie dann unserer Schritt-für-Schritt-Anleitung oder schauen Sie sich die Kurzanleitung an.
Note: This way only works for Excel file in .xlsx format.
Hence, if your Excel workbook is in .xls format, just open it and then save it as .xlsx format.

Step 1: Change the Excel file name extension from .xlsx to .zip. Just click on Yes to make sure when prompted.

Step 2: Open the ZIP archive as 7-ZIP. Open the folder xl->worksheets and you can see threesheet.xml files. Select the sheet1.xml file. Right click on it and select Edit. Choose to open it withNotepad.

Step 3: Delete the tag beginning with sheetProtection. Then save and close the sheet1.xml file.

Step 4: Update the modified sheet1.xml file in the ZIP archive when prompted. Then close the ZIP archive.

Step 5: Change the ZIP fie name extension back to .xlsx. At this point, the Excel sheet has been unprotected. Open it and you can edit the sheet without password.

@NikolinoDE Hi.. thanks for your prompt reply.

 

This is my worksheet ONLY. I want to avoid keeping it desktop and using it from OneDrive but i can't keep that file in OneDrive without password protected. 

 

when I was using from desktop i had no issues and even if i download from onedrive and load into desktop using a USB, i am able to use it perfectly. no issues. 

 

i think you are right, we must have to use VBA coding to have a password prompt before opening the excel sheet (its in .xlxs format) as normal excel feature is not working in office 365..

 

if you can help with VBA code that can help, it will be awesome... thanking in advance...

@amitmahapatra 

Instructions:
Open your own password-protected file in Excel.
Then open a new Excel file (workbook) in parallel.
Open VBA in the newly opened file (ALT + F11).
Select worksheet, copy in code in a sheet and execute macro (F5).
After a short (or long time, depending on the size of the file and the password), a message box appears with the text "Finished".
Save your file (the one with the ex password) and close everything. Select the file with the ex password, open it and everything works without a password.
 
Please don't forget to Vote and Mark in the Community forum, my post, as a "right answer" and mark it with a "like" too . As it will be beneficial to more Community members reading in forum of the Community.
 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE Hi Nikolino .. my apologies for the confusion.

 

what my requirement is to be able to use the same Password protection while opening the excel in office 365 in similar way, i am currently using in desktop version of excel.

 

I know that with office365 you can use the password option by opening the excel in desktop mode but in my office i can't download anything from OneDrive to desktop.. so i am not able to use the excel by opening through password, else i know the password and i can open it in my personal laptop... its just in my office laptop i can't...

 

so i am looking for a VBA solution which i want to use in the excel to ask for password before i open the excel in office365 without downloading to desktop excel application.  I want to keep the password asking option before opening the excel.

 

I hope this one is clear now.

@amitmahapatra 

As far as I've tried with my Excel for Web, if it can't open from OneDrive, he asks whether he can / may open the file from a desktop version and it works without problems. My Excel desktop then opens automatically without problems and asks for the password as usual.


Please update your PC and try again.


Some Further information below for your additional Info.

 

Work with VBA macros in Excel for the web

https://support.microsoft.com/en-us/office/work-with-vba-macros-in-excel-for-the-web-98784ad0-898c-4...

Excel for the web

https://docs.microsoft.com/en-us/office365/servicedescriptions/office-online-service-description/exc...

 

Sorry that I couldn't help anymore.

Thank you for your understanding and patience

 

 

Nikolino

I know I don't know anything (Socrates)

 

Hi, yes you are right. I can do that in my own pc but not in office laptop because my company has blocked to download anything from One drive.. So I can update or upgrade anything in office laptop to work this...

The only way for me is to remove the normal password and have a VBA password encoded in the excel that will ask for password when open in office365 from one Drive on Web..

Can you please help with a code in VBA that can be used to ask for password when opening a Excel sheet? This is exactly what I need..

@amitmahapatra 

 

My last thought is a manual solution so that you are also compliant with your company regulations. A USB stick from home with the files you want to edit at work and back at home with the stick to overwrite the existing files in your OneDrive.

It is tedious, but bypassing all possible regulations that are on your way. At the same time you are on the safe side that you are not breaking any company rules.

It's just a thought :)
 

Sorry that I took your time and couldn't give you a quick solution.

 

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

Thanks for your suggestions.. Really appreciate it..

Well I was not asking to break the rule rather i will follow the rule by not downloading it to desktop and still able to use password to open the file.. Anyways let's leave it..
Sorry for misunderstanding me, didn't say you wanted to break the rules. I just said that you are on the safe side and that you can keep the rules safely.

Anyway, my mistake if it's gotten wrong.
Must improve my english to avoid misunderstandings.

I wish you a nice day and much success for a solution of your project.

Nikolino
I know I don't know anything (Socrates)
How i apply new password keonglee8@outlook.com

@NikolinoDE 

 

I have tried several times and it does not work 

There is no easy solution in this case. You want a password protected file to unlock the password in Excel for web. Before that, you are bound by your company's rules not to open the protected OneDrive.
The simplest solution would be to buy an O365 subscription.
I can only offer the solution with zip, as I sent you at the beginning, which you probably threw without trying.
My knowledge ends here, maybe someone here in the forum knows more and can help you across the veins.

I wish you much success in your project.

Thanks for your time and understanding

@NikolinoDEI tried this. Did not see any sheet xml files as described 

how do you open your own password protected file if you don't have the password. If you don't have it, it won't open

@Rbehrens63 

As I said, try different ways to open it.

If ziping doesn't work, then google sheet ... or with secret ingredients :))

 

Here are the links again

1Link: Password Options

2 Link: How to unprotect the excel sheet if forgot the password

 

Thank you for your patience and time.

 

Nikolino

I know I don't know anything (Socrates)