vbscript - use vba/vbs to open xlsx with password and save into a new xlsx

Question

Have a use case. There is a password-encrypted xlsx. The password is for reading the workbook only, and I cannot make any edit or remove the password.

I need an xlsx without password so downstream processing can take the info from there. What I'm thinking is to open a new workbook and save the data there, but it always got stuck at the step where it's trying to open the encrypted workbook. Any idea?

Sub OpenXlsx()
set objExcel = Create.Object("Excel.Application")
path = "c:/myfilepath.xlsx"
newpath = "c:/12myfilepath.xlsx"
' next line Not Working. It's able to open the 
' excel, but Excel is still stuck at the screen 
' to enter password, and 'excel is waiting for another 
' application to complete an ole action'
set wb = objExcel.Workbooks.Open(path, Password = "123")
set wb1 = objExcel.Wookbooks.Add()

wb.Sheets(1).cells.copy
wb1.Sheets(1).cells.paste PasteSpecial:=xlPasteValues
wb1.SaveAs(newpath)
wb.Close
wb1.Close
objExcel.Quit

End Sub

question from:https://stackoverflow.com/questions/65641466/use-vba-vbs-to-open-xlsx-with-password-and-save-into-a-new-xlsx

Answer 1

Since the extension is xlsx it's the Microsoft Excel Open XML Format. That is essentially a .zip file. Try this:

1. Copy the original file to a new folder so you don't tamper the original
2. Rename the file to a .zip extension
3. Extract the content with winzip or the like
4. In the extracted content, open xlworksheetssheet1.xml as a text file with notepad or the like
5. Search for <sheetProtection
6. Remove all from <sheetProtection to and including the terminating /> and save.
7. Save the content as a .zip
8. Rename to .xlsx
9. Open the file. Password should be gone.

Excel 2016 file passworded.xlsx renamed as passworded.zip opened in WinZip: