Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Other Programming > VBScript
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 5th, 2003, 07:32 PM
Registered User
 
Join Date: Oct 2003
Location: cuperino, ca, USA.
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
Location: , , United Kingdom.
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
Location: cuperino, ca, USA.
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
Location: , , United Kingdom.
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
Location: , , United Kingdom.
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, 03:50 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
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
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel functions in vbscript blrao VBScript 0 April 12th, 2008 05: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 05:35 PM
Saving MS-Excel sheets with VBScript shamigc Excel VBA 1 July 9th, 2004 09:11 AM
Unprotect a cell on Excel camf650 VBScript 2 April 14th, 2004 12:26 PM



All times are GMT -4. The time now is 02:29 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.