password in VBA

[Frank Situmorang]

Hello,

I am trying to understand how macro will write the password in VBAmby using
recording Macro for a protected password workbooks, because I am trying to
make a link base on the example 1 of Mr. Ron De Bruin, but I can not see the
VBA of the password itself:

Here is the VBA of my recording:
Sub testh()
'
' testh Macro
' Ini adalah untuk mengetest kalau pakai password
'
' Keyboard Shortcut: Ctrl+Shift+H
'
ChDir "\\Admin-hdd\budget-contr\BUDGET CONTROL M\BUDGET 2009"
Workbooks.Open Filename:= _
"\\Admin-hdd\budget-contr\BUDGET CONTROL M\BUDGET 2009\M10-9-006
ASAHI.xlsx", _
UpdateLinks:=0
End Sub


Thanks for any help.

Frank

 

[Dave Peterson]

Search VBA's help for "Open Method"
Then open the link for "Open method as it applies to the Workbooks object."

You'll see how you can pass parms to the .open statement:

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable,
Notify, Converter, AddToMru, Local, CorruptLoad)

So just like you passed an option (the updatelinks stuff), you can use:

Workbooks.Open Filename:= _
"\\Admin-hdd\budget-contr\BUDGET CONTROL M\BUDGET 2009\M10-9-006 ASAHI.xlsx", _
UpdateLinks:=0, password:="topsecretpassword"

 

[Frank Situmorang]

Thanks Dave for your response.
The problem Dave, because in the Ron's example, Getopenfile alrready
determinded the workbooks path, how can we make the password VBA fit with
this Getopenfile, because we like it, that we can link with anyother folder
without writing the file path, like you showed.

Thanks for giving me more explanantion,

Frank

 

[Dave Peterson]

You have to know the password for the file--no matter what the user chose.

And the password is a parameter on the .open line--not the .getopenfilename
line.

If you don't know the password for that file, maybe you could just ask the user
or show the open dialog and have the user do the work.

Dim res As Boolean
Dim wkbk As Workbook

res = Application.Dialogs(xlDialogOpen).Show

If res = False Then
MsgBox "nothing opened"
Else
Set wkbk = ActiveWorkbook
MsgBox wkbk.FullName
End If

 

[Frank Situmorang]

Sorry Dave...may be I have language problem, where shall we put this VBA
Workbooks.Open Filename:= _
"\\Admin-hdd\budget-contr\BUDGET CONTROL M\BUDGET 2009\M10-9-006
ASAHI.xlsx", _
UpdateLinks:=0, password:="topsecretpassword"
the password, and there are about 500 workbooks of the project with the
same password.

What I got in Rons's website is using GetOpen file without tupenging the
path, and we can just choose the files we want to link, and everythng is
done. So How can I fit it with your suggestion

Thanks very much

Frank

 

[Dave Peterson]

If you know the password for any file that the user can choose, then change it
in that workbooks.open line.

I used:
password:="topsecretpassword"

But you can replace that top secret password with what you need it to be.

Sorry Dave...may be I have language problem, where shall we put this VBA
Workbooks.Open Filename:= _
"\\Admin-hdd\budget-contr\BUDGET CONTROL M\BUDGET 2009\M10-9-006
ASAHI.xlsx", _
UpdateLinks:=0, password:="topsecretpassword"
the password, and there are about 500 workbooks of the project with the
same password.

What I got in Rons's website is using GetOpen file without tupenging the
path, and we can just choose the files we want to link, and everythng is
done. So How can I fit it with your suggestion

Thanks very much

Frank