Removing a password from a worksheet

funkyfido · #1 11-18-2013, 06:57 AM

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

excelledsoftware · #2 11-20-2013, 12:44 AM

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

BobBridges · #3 11-20-2013, 10:21 AM

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'?

funkyfido · #4 11-21-2013, 03:00 AM

Quote:

Originally Posted by excelledsoftware

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

That didn't work for me either.

funkyfido · #5 11-21-2013, 03:00 AM

Quote:

Originally Posted by BobBridges

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'?

Tried this but no joy either.

funkyfido · #6 11-21-2013, 03:17 AM

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

BobBridges · #7 11-21-2013, 07:25 AM

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.

funkyfido · #8 11-21-2013, 08:10 AM

Quote:

Originally Posted by BobBridges

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.

Sorry if I didn't reply correctly, not trying to upset you.
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

BobBridges · #9 11-22-2013, 11:29 AM

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.

BobBridges · #10 11-22-2013, 12:26 PM

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.

funkyfido · #11 11-25-2013, 09:19 AM

Quote:

Originally Posted by BobBridges

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.

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.

BobBridges · #12 11-26-2013, 05:59 PM

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.

macropod · #13 11-26-2013, 11:01 PM

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

Note that there is no need for the ChDir code.

funkyfido · #14 11-27-2013, 07:52 AM

Quote:

Originally Posted by macropod

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

Note that there is no need for the ChDir code.

Superb! That has solved my issue. Thank you so much.

BobBridges · #15 11-27-2013, 10:29 AM

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"

Once I have all the objects defined, then for the rest of the program I can refer to soIp.Cells, woOp.SaveAs and so on without worrying that my program will do something unexpected. That is, programs almost always do something unexpected, but this cuts down on the puzzlement.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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