Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Other Programming > VBScript
|
VBScript For questions and discussions related to VBScript.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VBScript section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 5th, 2003, 07:32 PM
Registered User
 
Join Date: Oct 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default unprotect excel in vbscript

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

thx

Reply With Quote
  #2 (permalink)  
Old November 6th, 2003, 05:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old November 19th, 2003, 04:37 PM
Registered User
 
Join Date: Oct 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old November 21st, 2003, 05:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old November 21st, 2003, 09:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6 (permalink)  
Old March 12th, 2007, 02:50 PM
Registered User
 
Join Date: Mar 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel functions in vbscript blrao VBScript 0 April 12th, 2008 04:46 AM
Protect / Unprotect Excel 97 - VBA Code - jlpazv Excel VBA 2 November 30th, 2007 10:54 AM
using vbscript in asp to import excel into access wpiass Access 11 April 26th, 2007 04:35 PM
Saving MS-Excel sheets with VBScript shamigc Excel VBA 1 July 9th, 2004 08:11 AM
Unprotect a cell on Excel camf650 VBScript 2 April 14th, 2004 11:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.