macro that inputs the password on a password protected document

[dandavis1]

Hi Guys,

Not too sure if this is even possible.

I have a number of hyperlinks in a document which when you click on them open up different documents. These are all password protected so when you click on any of the links it will ask you to input the password before the document opens. Would it be possible to create a macro so that when you click on one of these links the password then is automatically populated and opens the document straight away?

Any help appreciated. Cheers.

[ragulduy]

What sort of documents are they?

[dandavis1]

Hi,

The document where i have the hyperlinks is an excel document. All the hyperlinks open excel documents.

Thanks for quick response.

[ragulduy]

I would suggest:
Change the hyperlink so that it links to it's own cell. Then put some VBA code in to capture the follow hyperlink event and open the workbook in question using the password.

For example, in A1, if you want to have a hyperlink to E:\test.xls, then for the hyperlink in A1, put the text to display as "E:\test.xls" but put the link to a place in this document with a cell reference of A1.

Then put this code in the sheet object module of the vba project:

Please Login or Register  to view this content.

[dandavis1]

Think i have misunderstood your instructions. See attached

it was still prompting me for the password once i clicked on the hyperlink.

thanks again,

[ragulduy]

Hi - the code needs to go in the Sheet1 object, not a new module. Take a look at the attached example.

[dandavis1]

Oh yes it works. Thanks for all your help this afternoon. Very much apreciated!!!

[dandavis1]

Having thought about it some more, there is potential for more than one hyperlink to be on the sheet therefore meaning hyperlinks to different files with different passwords. How would this code work for multiple hyperlinks?

THanks very much.

[ragulduy]

Where would the passwords be stored?

[dandavis1]

The documents which will be hyperlinked are password protected. See attached an example of how one hyperlink would work, im just not sure how i would code for more than one especially if the documents have different passwords.

[dandavis1]

The documents which will be hyperlinked are password protected. See attached an example of how one hyperlink would work, im just not sure how i would code for more than one especially if the documents have different passwords.

Thanks!!

[dandavis1]

The documents which will be hyperlinked are password protected. See attached an example of how one hyperlink would work, im just not sure how i would code for more than one especially if the documents have different passwords.

Thanks!!

[ragulduy]

Hi - the "followhyperlink" event is not specific to one hyperlink. It will run whenever any hyperlink on the sheet is clicked.

Therefore, if all the hyperlinks have the same password, it should function ok for more than one hyperlink (providing you set all the links up as described previously).

If the documents have different passwords then the macro needs to find out what the password is for the link that has been clicked - hence the question of where the passwords are stored. How does the code know what password to open the file with? Are they written into the code? are they stored in a look-up table? Are they contained as part of the hyperlink properties (e.g. the texttodisplay is something like "E:\Data.xls :: password"

Note - perhaps one solution is to remove the passwords from the files?

[dandavis1]

Apologies for the repeated messages. Computer playing up!!

Unfortunatly the files are in a public folder so need to keep the protection on them.

So if i stored the passwords in a table on this main document and made the table hidden could this work using the similar method currently being used on the document? Here is another example of my interpretation...

[ragulduy]

See attached example

[dandavis1]

Thanks for your example, will i need to copy the code and paste it so it has one for each hyperlink? At the moment it doesnt matter what hyperlink i seem to click it keeps opening the same file???