Linking To Password Protected Worksheeets

[Gettingbetter]

Hi Everyone

I have searched and searched for an answer to this but can not find one.

I run a macro that inserts links into cells,

What I need is, when linking to a spreadsheet that is password protected you get prompted for the password. Is there any way you can add the password to your code so that the user does not have to keep putting in the password.

I am hoping I do not have to open the spreadsheets im linking to as there big.

Cheers

 

[Harvey]

try opening the workbook by means of code, then place the links, and after close it. I'm not sure if you can close it though, but i hope so

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    Const PWord As String = " "
    SendKeys PWord & "{Enter}"
    ActiveCell.Formula = _
        "='P:\TEMP\MrExcel\FileLinksPWord\[Source.xls]Sheet1'!A1"
End Sub

The password for Source.xls was a single space. Change the constant PWord to suit.

 

[Gettingbetter]

Andrew you are a superstar

The sendkeys works perfectly...

Just another little thing... If the spreadsheet is open then the sendkeys obviously is not needed.. Do you know a variation of the below code that works so that I can make it miss sendkeys if the workbook is open

'this code does not work
if Workbooks.Open Filename:= _
"C:\password.xls" = true then exit sub

I have tried loads of combinations but cant get it to work

Cheers

 

[Harvey]

for each w in workbooks
  if w.name = myWorkbookname then isitopen = true
next w

 

[Gettingbetter]

Cheers Harvey

That works perfectly

You and Andrew are Excel Gods ray: ray: ray: