Want to open a file on a network via a hyperlink in a cell and passing in a password 1

[Baggie]

Hi,
I have been searching for awhile but cannot find what I am trying to perform. I haven't programmed in VBA in awhile and never in Excel VBA, so I'm not handy with the objects, events etc. in it.
I have a spreadsheet with a bunch of summary information (about 500 rows) in it that some auditors are going to look at. In the last two columns of any particular row I have a pathway to a specific file which contains more detailed information about the row record. So, if an auditor has a question they can click the hyperlink and get to the file. In the column to the right is the password to open the specific file. As a convenience to the auditor I'd like to create a macro so that when they click the hyperlink and it automatically puts the password in there so that they don't have to input it.

Here's a screen shot of the two columns:
Hmmm... can't get a screen shot to work, looks like this:
Cell A has a hyperlink like: Filepath\filename.xls, Column B has a numeric password like: 12345

So, what I've done so far that does the action I want is this code. However, I want to be able to just click in the cell with the hyperlink on whatever row I am on and then open that file and pass in the password. Hoping that someone can give me a few pointers. Thanks in advance for any assistance!!

Sub OpenBooks()
Dim wkb1 As Workbook
Dim Cell As Range


For Each Cell In ThisWorkbook.Sheets("Sheet1").Range("A1")
    Set wkb1 = Workbooks.Open(Filename:=Cell.Value, Password:=Cell.Offset(0, 1).Value, writerespassword:=Cell.Offset(0, 1).Value)
Next Cell

End Sub

 

[Baggie]

I have continued researching and now I have found the workbook doubleclick event. I'm thinking that this may be better. However, now I need to limit the range to just the column with the network file pathways so if they double click on any other area it wont' fire off the event. Anyone have an idea on how to perform that?
Thanks!

Sub worksheet_beforedoubleclick(ByVal target As Range, cancel As Boolean)
Dim Cell As Range
Dim wkb1 As Workbook

Set Cell = Application.ActiveCell
If target = Cell Then
    cancel = True
    Set wkb1 = Workbooks.Open(Filename:=Cell.Value, Password:=Cell.Offset(0, 1).Value, writerespassword:=Cell.Offset(0, 1).Value)
End If

End Sub

 

[SkipVought]

hi,

First make your table a Structured Table. For this example, I chose to name the table tfiles, with headers Filepath and PW.

Here is the code, based on that assumption...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim wkb1 As Workbook, rPW As Range
    
    If Not Intersect(Target, [tFiles[FilePath]]) Is Nothing Then
        Set rPW = Intersect(Target.EntireRow, [tFiles[PW]])
        Set wkb1 = Workbooks.Open(Filename:=Target.Value, Password:=rPW.Value, writerespassword:=rPW.Value)
    End If
    
    Set rPW = Nothing
    Set wbk1 = Nothing
End Sub

BTW, is there any reason to set the opened workbook to the wkb1 Workbook Object?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Baggie]

Hi Skip,
Thanks for your response. I will give this a try and let you know how it goes. As far as the workbook object is concerned I chalk that up to inexperience. Are you saying that I don't need to assign it to anything? I can just use the workbook.open...etc without setting it to wkb1 workbook object?
Thanks again.
Baggie

 

[SkipVought]

Yes

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Baggie]

Hi Skip,
Finally got back to this and glad to say it worked like a charm. Just had to add the cancel = True part inside the If statement. Wish I could change the title of this thread in case anyone else is looking for a solution like this.
Thanks so much for your help.
A star for you!
Baggie

 

[SkipVought]

Wonderful!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}