unlock workbooksin hundred of excel files

[guilhaume]

Hi all,

I'm trying to get data from hundred of excel files with Java.
The workbooks are locked (so I cannot get the data, and there is n
Java-solution), I know the password, but I don't want to unlock the
manually

Does anybody know a script (I don't know vb very well...) that can d
this job for me?

reguard

 

[Tom Ogilvy]

Dim bk as Workbook, sPath as String
Dim sStr as String
sPath = "C:\MyFolder\"
sStr = Dir(sPath & "*.xls")
do while sStr <> ""
set bk = workbooks.Open( filename:=sStr, _
password:="ABCD")
' work with bk
bk.close Savechanges:=False
sStr = Dir()
Loop

 

[guilhaume]

Thx a lot, but I think I didn't explain very well what I wanted to do

It is to say:
have a script that takes out the workbook's protection without havin
to open the excel file with Office and do 'Utils', 'Protection'
'Unprotect the workbook' (problably the menu names are wrong, as I a
french I tested a translation... )

in fact I wanted a script like that:

for all the files f in the directory d do
f.unprotectWorkbook()
end

reguard

 

[sp00nix]

Here's some code i just wrote for work that unlocks each sheet within
workbook. This is in a module and will help you get started:


Code
-------------------

Const strLock = "secretPassword"
Sub showForm()
frmGetPass.Show
End Sub

Sub protectAll()
'
' MACRO to protect all work-
' sheets in the active book
'----------------------------
Dim Wb As Excel.Workbook
Dim sheet As Excel.Worksheet

Set Wb = Application.ActiveWorkbook

Application.ScreenUpdating = False
For Each sheet In Wb.sheets
sheet.Protect strLock
Next sheet
Application.ScreenUpdating = True
End Sub
Sub unprotectAll(myPassword As String)
'
' MACRO to unprotect all work-
' sheets in the active workbooks
'--------------------------------
On Error Resume Next
If myPassword <> strLock Then MsgBox "Invalid Key"
If myPassword = strLock Then MsgBox "Success!"

Dim Wb As Excel.Workbook
Dim sheet As Excel.Worksheet

Set Wb = Application.ActiveWorkbook

Application.ScreenUpdating = False
For Each sheet In Wb.sheets
sheet.Unprotect myPassword
Next sheet
Application.ScreenUpdating = True

End Su
-------------------



and i have a form with the following to get the pass from the user:


Code
-------------------

Private Sub CommandButton1_Click()
If TextBox1.Text <> "" Then protection.unprotectAll (TextBox1.Text)
Me.Hide
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Text <> "" Then protection.unprotectAll (TextBox1.Text)

Me.Hide
End Sub

Private Sub UserForm_Activate()
TextBox1.Text = ""
TextBox1.SetFocus
End Sub

 

[Tom Ogilvy]

Dim bk as Workbook, sPath as String
Dim sStr as String
sPath = "C:\MyFolder\"
sStr = Dir(sPath & "*.xls")
do while sStr <> ""
set bk = workbooks.Open( filename:=sStr, _
password:="ABCD")
Application.DisplayAlerts = True
bk.SaveAs bk.FullName
Application.DisplayAlerts = False
bk.close Savechanges:=False
sStr = Dir()
Loop

 

[Tom Ogilvy]

sorry - that was file level protection. For workbook protection as you
describe

Dim bk as Workbook, sPath as String
Dim sStr as String
sPath = "C:\MyFolder\"
sStr = Dir(sPath & "*.xls")
do while sStr <> ""
set bk = workbooks.Open( filename:=sStr)
bk.Unprotect Password="ABC"
bk.close Savechanges:=True
sStr = Dir()
Loop

--
Regards,
Tom Ogilvy

Dim bk as Workbook, sPath as String
Dim sStr as String
sPath = "C:\MyFolder\"
sStr = Dir(sPath & "*.xls")
do while sStr <> ""
set bk = workbooks.Open( filename:=sStr, _
password:="ABCD")
Application.DisplayAlerts = True
bk.SaveAs bk.FullName
Application.DisplayAlerts = False
bk.close Savechanges:=False
sStr = Dir()
Loop

 

[guilhaume]

thanks a lot to all of you!

I think it will greatly help me

 

[guilhaume]

hey,
I was on holiday..that's why I didn't test the code earlier!
when I try to run this code:

Private Sub Form_Load()
Dim bk As Workbook, sPath As String
Dim sStr As String
sPath = "G:\gmp\fichiers xls\"
sStr = Dir(sPath & "*.xls")
Do While sStr <> ""
Set bk = Workbooks.Open(FileName:=sStr)
bk.Unprotect Password = "2132"
bk.Close Savechanges:=True
sStr = Dir()
Loop
End Sub

I have the following error
(in french for those who will understand)
Erreur d'execution '1004'
toto.xls est introuvable, verifiez l'orthographe du nom du classeur e
la validité de l'emplacement
(here is an english translation )
Runtime error '1004'
toto.xls not found, verify the workbook name and the path

but toto.xls exists at the specified path....

I searched on the net and I didn't find what it meant!

Could you help me please?
regards
guilhaum

 

[Alex T]

guilhaume

I'm pretty sure that your file name is not correct.

From yur code I read

sPath = "G:\gmp\fichiers xls\"

which should be

sPath = "G:\gmp\fichiers.xls"

(no trailing slash and a dot for the file extension)

Hope it helps

--alexT

 

[guilhaume]

Alex,

I am really sure that the path is correct
in fact this:

sPath = "G:\gmp\fichiers xls\"
represents the directory,
and

sStr = Dir(sPath & "*.xls")
represents the file name in the specified directory

in fact my directory name is like "excel dirctory"
lol
so I don't think the error comes from here (but as I am not perfect
perhaps I'm wrong )

but thanks anyway for answering!

guilhaum

 

[Dave Peterson]

Try this (with a correction to a small typo, too):

Option Explicit

Private Sub Form_Load()
Dim bk As Workbook, sPath As String
Dim sStr As String
sPath = "c:\my documents\excel\"
sStr = Dir(sPath & "*.xls")
Do While sStr <> ""
'added "spath &" on next line
Set bk = Workbooks.Open(Filename:=sPath & sStr)
'added : to next line
bk.Unprotect Password:="2132"
bk.Close Savechanges:=True
sStr = Dir()
Loop
End Sub

 

[Dave Peterson]

I changed the sPath for my testing and forgot to change it back:
sPath = "G:\gmp\fichiers xls\"

 

[guilhaume]

yoooohoooo it works!!!!

Dave, thanks a lot !

You cannot imagine how much it helps me!

thanks again ;