Password to Open workbook : automation

[brownbread]

Hi all
I'm very lost and looking for advice.
I have several workbooks that are password protected - meaning I have to enter a password just to open them. The password is simple, "abc".

I get frustrated with opening spreadsheets then having to divert myself to the keyboard to type. Is there any way of automating this process within Excel? For example... I'd like to put some sort of code in my personal.xlsb, that runs every time I open a new workbook... it would essentially check if there is a 'Password to Open' situation, then it would attempt to enter the password "abc". Of course, if I ever opened a file with a different password, this code would simply fail - no big deal.

That's my theory. But I have no idea how to put it into practice - and I've been trying for a very long time.

Thank you

 

[Mohammad Basem]

Try this. You can assign the macro to a button on the toolbar,

[FONT=Consolas][SIZE=2][COLOR=Navy]Option Explicit
 
Sub OpenWokbook_ABC()
 
    Const csMyPassword As String = "abc"
 
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        If .Show = -1 Then
            Workbooks.Open Filename:=.SelectedItems(1), Password:=csMyPassword
        End If
    End With
 
End Sub[/COLOR][/SIZE][/FONT]

 

[brownbread]

Thanks for this, this is a lot further than I've got alone. It's a good step. The only thing is, it takes time to find each file I want through the 'Open' dialogue... I have project folders all over a server, and I will work in one folder at a time, using multiple files from each folder... so I always just double-click to open, because using the software's Browse/Open function isn't practical. Is there a way of modifying this code to make it run every time I open a file my own way?

 

[Mohammad Basem]

Once you open the first file, the selected folder will be current folder and, therefore, successive calls to the macro will open the current folder to pick the files.

I think this is same as what you have described, or am I missing something?

Do you want to pre-set the initial folder?

 

[brownbread]

That is very true, but I still need to find that first file, just the very first time at least... and we're talking about project folders buried four, five, six levels down on a networked drive. I mean, right now I double-click on a file and I'm having to type a three letter password. It's the time that it takes me to enter three letters and press enter, that I am trying to cut down! I realise I'm asking a lot out of Excel : ). I really just want to be able to double-click on the file from Win Explorer and have Excel enter the password as it opens.

Your code is brilliant, I'm just wondering if I can expand it to do this... I don't know. What do you think? It's all about saving time.
Thank you

 

[tusharm]

Modify Mohammad's code to allow multiple selections. On how to do that search Excel VBA help for 'FileDialog' (w/o the quotes) then check the 'FileDialog.SelectedItems Property' help page.

That is very true, but I still need to find that first file, just the very first time at least... and we're talking about project folders buried four, five, six levels down on a networked drive. I mean, right now I double-click on a file and I'm having to type a three letter password. It's the time that it takes me to enter three letters and press enter, that I am trying to cut down! I realise I'm asking a lot out of Excel : ). I really just want to be able to double-click on the file from Win Explorer and have Excel enter the password as it opens.

Your code is brilliant, I'm just wondering if I can expand it to do this... I don't know. What do you think? It's all about saving time.
Thank you

 

[Mohammad Basem]

I really just want to be able to double-click on the file from Win Explorer and have Excel enter the password as it opens.

I think you cannot control this from within Excel as Excel events will receive control (called) after the workbook is opened, which means, in your case, the password is entered.

You may think of other scenarios on how to facilitate your work from within Excel and we can discuss further.