Removing a password from a worksheet

macropod · #1 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 · #2 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 · #3 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.

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