#1
|
|||
|
|||
Removing a password from a worksheet
Hi
I am using VBA to copy over a range of workbooks to a new location. I want to clear the data from the worksheets which are password protected. I do know the password. Everything works okay until I insert ActiveSheet.Unprotect Password:="Test" then I get a subscript out of range message. Does anyone know how to get around this? Code:
Sub test() Workbooks.Open Filename:="c:\Try this.xlsm", Password:="one", WriteResPassword:="two" ChDir "c:\New\" ActiveWorkbook.SaveAs Filename:= _ "C:\New\Try this 1.xlsm", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False ActiveSheet.Unprotect Password:="Test" End Sub |
#2
|
|||
|
|||
Interesting. The ActiveSheet.Unprotect Password:"Test"
works just fine for me. There are 2 things you can try. First ensure that "Test" is indeed suppose to be capitaized. next you could try creating a variable and then using that for the password. Code:
dim pass as string pass = "Test" ActiveSheet.Unprotect Password: pass |
#3
|
||||
|
||||
If ActiveSheet.Unprotect earns a subscript error, then I wonder whether there's no ActiveSheet just at the moment. Sounds silly, but after all that's what a subscript error should mean in that context. What happens if you try pointing to the sheet using the sheet name instead of ActiveSheet, say 'MyWorkbook.Sheets("MyWorksheet").Unprotect'?
|
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
Quote:
Tried this but no joy either. |
#6
|
|||
|
|||
This is the code I have changed to but still getting problems.
Code:
Sub test() Workbooks.Open Filename:="c:\My Documents\Try this.xlsm", Password:="one", WriteResPassword:="two" ChDir "c:\New\" ActiveWorkbook.SaveAs Filename:= _ "C:\My Documents\New\Try this 1.xlsm", FileFormat:= _ xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False Sheets("sheet1").Activate ActiveSheet.Unprotect Password:="Test" End Sub |
#7
|
||||
|
||||
Two requests, funky:
1) In general, when you say things like "didn't work", "no joy" and "still getting problems" you leave us without clues. The first time was better; you said "I get a subscript out of range message". Now maybe the subsequent three times you meant "I get the same message", but speaking for myself I'd much prefer too much detail to too little. Something like this: "It displayed the message 'Subscript out of range', and when I hit the Debug button it showed the following statement...." (so that we know not only the exact text of the message but also which statement caused it). I recommend you make that kind of problem reporting a general habit. 2) I cut and pasted your first program into a workbook, then created a protected workbook with a password-protected sheet and ran your program on it (substituting only my path for "New"), and I didn't run into your problem, so I guess my theory was wrong. Can you post the workbook itself—preferably both of them? I may as well try it with the original; if I can't reproduce the problem then, we may be stymied. |
#8
|
|||
|
|||
Quote:
I have attached the files which I used. Test.xlsm contains the macro to open try this.xlsx and rename it and unlock the cells. The error which I receive is Microsoft Visual Basic box with just 400 |
#9
|
||||
|
||||
Oh, don't worry about upsetting me, funky. Some people are just too eager to get upset. I'm just saying that it'll help us help you if you keep both those in mind.
I'll check out those two workbooks and see what I can see. Sorry for the delay; busy week. |
#10
|
||||
|
||||
It's probably not related to your problem, but when I try to run this I get a problem in a different place. You're opening [Try this.xlsm], but when I download it and save it, its name is [Try this.xlsx].
I tried renaming the downloaded workbook as [Try this.xlsm], but Excel balked; it says "Run-time error '1004' / Application-defined or object-defined error". So I tried again by changing the name in the program to [Try this.xlsx]. That opened the workbook just fine, and then consented to save it as [Try this 1.xlsm]. But I didn't get your problem with the last statement, either. Before I give up, let me try asking questions from another angle: Where are you when you run the "test" macro, and what process do you use to invoke it? Which workbook and sheet is active, what keystrokes or clicks do you use, etc? Oh, and let's have a better description of the error, too. "Microsoft Visual Basic box with just 400" doesn't convey the right picture to me. |
#11
|
|||
|
|||
Quote:
I am using the test.xlsm document to open the file try this. It then saves it in a new directory and tries to remove the sheet password to perform edits. I have attached a screenshot of the error. Nothing more that i stated i'm afraid. |
#12
|
||||
|
||||
Wow, you weren't kidding! I assumed there was text along with that error.
I did a little googling and found these references. Maybe one will help: http://www.add-ins.com/support/micro...-error-400.htm http://social.msdn.microsoft.com/For...p?forum=isvvba http://www.excelforum.com/excel-prog...-400-mean.html http://excel.questionfor.info/q_micr...el_141010.html (this one has a list of a dozen or so links to similar-sounding questions) I didn't read them all carefully (just enough to see that there were some suggestions in each about what to do), but I notice that at least two say it may have to do with an add-in that needs to be fixed, perhaps uninstalled and reinstalled. That would go along with the fact that two of us couldn't reproduce the error on our own machines. |
#13
|
||||
|
||||
I suspect your basic issue is that you have unqualified sheet & workbook references (plus you're code is telling Excel to open an xlsm file whereas the one you provided for testing is an xlsx file). Try:
Code:
Sub Test() Dim xlWkBk As Workbook Set xlWkBk = Workbooks.Open(Filename:="c:\My Documents\Try this.xlsx", _ Password:="one", WriteResPassword:="two", AddToMRU:=False) With xlWkBk .SaveAs Filename:="C:\My Documents\New\Try this 1.xlsm", _ FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False, AddToMRU:=False .Sheets("Sheet1").Unprotect Password:="Test" End With End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Quote:
|
#15
|
||||
|
||||
Aha! Good call, macropod. Funky, this is why I've gotten into the habit of qualifying almost everything in VBA; there are just too many ways to lose track of what the default may be. Besides, a lot of programs I write work with multiple worksheets, workbooks etc. So I find it a useful habit to just define a variable for each object I'll be working with, something like this:
Code:
Set woIp = ThisWorkbook Const wnOp = "TLXI.xlsx" If Not Exists(Workbooks,wnOp) Then Abend "Can't find workbook " & wnOp Set woOp = Workbooks(wnop) Set soIp = woIp.Worksheets("Misc") woOp.Worksheets.Add set soOp = ActiveWorksheet soOp.Name = "New" |
Tags |
password, remove password |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Password protected file no longer offers password dialog on Open | htaylor | Word | 0 | 10-15-2013 12:35 PM |
How to summarise different worksheet to a summary worksheet | samkiewhock | Excel | 1 | 09-06-2012 03:34 AM |
Outlook requests password afeter changing windows password | pask | Outlook | 5 | 03-16-2012 08:43 AM |
Removing password protection | HantsDave | Word VBA | 9 | 03-08-2012 11:57 AM |
Trouble Removing Password | cure4glass | Word | 1 | 02-17-2012 07:19 PM |