unprotect excel in vbscript

emmydotnet · #1 (**permalink**) November 5th, 2003, 07:32 PM

I am uploading a protected workbook into mssql using dts, and i need to unprotect the workbook before firing the dts. any ideas?

thx

pgtips · #2 (**permalink**) November 6th, 2003, 05:52 AM

Here's a bit of VBS that will unprotect a worksheet:

Code:

Dim objExcel
Set objExcel = WSript.CreateObject("Excel.Application")
objExcel.Workbooks.Open "<<file name and path here>>"
objExcel.ActiveSheet.Unprotect Password:="<<password here>>"
objExcel.Workbooks(1).Save
objExcel.Workbooks.Close
Set objExcel = Nothing

hth
Phil

emmydotnet · #3 (**permalink**) November 19th, 2003, 04:37 PM

for some reason, i always get an incorrect password error. is there something i the syntax that I am missing?

pgtips · #4 (**permalink**) November 21st, 2003, 05:06 AM

Maybe its this line:

Code:

objExcel.ActiveSheet.Unprotect Password:="<<password here>>"

The code I pasted was VB and I'm pretty sure VBScript doesn't support named arguments, so change it to

Code:

objExcel.ActiveSheet.Unprotect "<<password here>>"

I'm confused though, the original would just give you a syntax error, unless you have On Error Resume Next I guess ?

hth
Phil

pgtips · #5 (**permalink**) November 21st, 2003, 09:17 AM

There's also a typo in line 2:
Set objExcel = WSript.CreateObject("Excel.Application")
it should be WScript not WScipt

Sorry about that

I just had a thought - Excel offers different levels of protection. I assumed you meant the worksheet had password protection (Tools > Protection > Protect Sheet), but maybe you meant that it has a password specified in the SaveAs dialog when the file was first created? If this is the case you can only remove the password by saving it as a new file name, like this (hope for no typos :))

Code:

Option Explicit
Dim objExcel
Set objExcel = WScript.CreateObject("Excel.Application")
objExcel.Workbooks.Open "<<name and path>>",,,,"<<password>>"
objExcel.Workbooks(1).SaveAs "<<new name>>",,""
objExcel.Workbooks.Close
Set objExcel = Nothing

rgds
Phil

jchen · #6 (**permalink**) March 12th, 2007, 02:50 PM

Hi Phil or anyone,
I need to unprotect password on each worksheet in excel files. What is the syntax to loop through each worksheet? Thanks