VBA Code to remove passwords

[tarynmahon]

I have a Macro that takes a Password Protected workbook and does certain things to it then saves it in another area, I would like to remove both read and modify passwords on the newly saved version, I tried to record the action of me doing Save as - tools - general options and deleting the passwords but it didnt seem to pick it up in the code, any ideas please?

I am extremely new to Macros so please reply in idiot terms.


Thanks

 

[bobhc]

Good day tarynmahon


I think you can find what you are after in the Vault

 

[tarynmahon]

I've tried but can only see things about removing the password set on VBA, if you could offer some clues as to what to type in to find it that would be much appreciated

 

[NARAYANK991]

Hi ,


I am not clear on what you wish to do ; if you have a password-protected workbook , which you can open , then it clearly means you know the password. If so , can you see the VBA code which is working on the workbook , or can you not ( since the VBA code may be protected , through a different password ) ?


If you can see the VBA code , then just copy + paste the entire VBA code here , and we can remove the statements which are saving the workbook with the password ; you can then replace your workbook code with the modified code , and you should get what you want.


If you cannot see the VBA code , then check out the following links :


1. http://stackoverflow.com/questions/272503/how-do-i-remove-the-password-from-a-vba-project


2. http://davidbugden.com/?p=16


If you find this too intimidating , just upload your workbook , and someone will do it for you , and re-upload.


Narayan

 

[tarynmahon]

[pre]

Sub NEWSNAPSHOT()
'
' SNAPSHOT Macro
'

'
Dim DATETEXT As String

DATETEXT = Range("DATETEXT")

Sheets("Board").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Application.CutCopyMode = False

Sheets("Trust").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B16").Select
Application.CutCopyMode = False

Sheets("Dashboard").Select
Columns("K:K").EntireColumn.AutoFit
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

Sheets("P&L").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3:B4").Select
Application.CutCopyMode = False

Sheets("C.CP&L").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False

Sheets("ProfCentreP&L").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B8:B9").Select
Application.CutCopyMode = False

Sheets("O.HP&L").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5:B6").Select
Application.CutCopyMode = False

Sheets("YLP&L").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B5:B6").Select
Application.CutCopyMode = False

Sheets("DetailP&L").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False

Sheets("BS").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4").Select
Application.CutCopyMode = False

Sheets("Check").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3:B4").Select
Application.CutCopyMode = False

Sheets("Front Sheet").Select
Range("B11:I11").Select

ActiveWorkbook.SaveAs Filename:="F:FinanceAccountsAccounts2013SNAPSHOTS" & "SNAPSHOT " & DATETEXT & ".xlsm"

End Sub

[/pre]

Thank you

 

[NARAYANK991]

Hi ,


The VBA code by itself does not mention any passwords ; do you mean to say that when you try to open one of the saved files from the :


F:FinanceAccountsAccounts2013SNAPSHOTS


folder , it has a password for read and modify ?


Narayan

 

[Colin Legg]

Try changing your SaveAs line to this:

[pre]

ActiveWorkbook.SaveAs _
Filename:="F:FinanceAccountsAccounts2013SNAPSHOTS" & "SNAPSHOT " & DATETEXT & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False

[/pre]
This will strip out the existing passwords when the file is saved.

 

[tarynmahon]

Perfect Colin, thank you

 

[SirJB7]

@all

Hi!

The old method described in previous link http://davidbugden.com/?p=16 of using hex editors to change DPB string by DPx works only for older versions of Excel files up to 2003 (i.e., .xls files), and doesn't work for 2007+ .xlsm files. Not even saving it previously as .xls, editing, reopening and ignoring errors, as in one of the comments in that link is stated.

Regards!