VBA to link file to source in protected folder

[asad]

Hello Guys,

I wanted a file to work with the link to the other file. The problem will arise when we put the source file in a folder only accessible if the user is logged on to the network as the Operations Manager. If anybody else other than the Manager logs on to the same computer, he cannot access that folder. The file with the link updates values from that source file only and only if the source file is accessible to the user. Otherwise it gives a message that source file is not accessible and all the values remain blank.

How can I get the code to connect to the file within the protected folder?
I do have the option of putting in user name and password within the code and make the VBA code also password protected so that no user can open the code and get the password. But how do I do it?

I will post the code that I want to add this option to.

Private Sub CommandButton1_Click()
' Macro recorded 25/05/2011 by Ali
'
Dim myPath As String
    Dim myFormulaPart1 As String
    Dim myFormulaPart2 As String
    Dim myFormulaPart3 As String
    myPath = "E:\Ali Land\MrExcel\Footscray Roster\[Leave  allocation 2011 Master.xls]Leave Approved"
    
    myFormulaPart1 = "=IF(RC[-6]<>""A/L"","""",""A/L ""&TEXT(MIN(IF('" & myPath & "'!R15C9:R215C9=RC[-2],X_X_X)),""dd/mm/yyyy""))"
    myFormulaPart2 = "IF('" & myPath & "'!$I$4:$FQ$4>=$BC$1,IF('" & myPath & "'!$I$15:$FQ$215="""",Y_Y_Y))))"
    myFormulaPart3 = "'" & myPath & "'!$I$4:$FQ$4-7"
[BC1] = [BC2].Value
[E8:F35].Copy [E9:F36]
[E36:F36].Copy [E8:F8]
[E40:F63].Copy [E41:F64]
[E64:F64].Copy [E40:F40]
[E68:F72].Copy [E69:F73]
[E73:F73].Copy [E68:F68]
[E77:F81].Copy [E78:F82]
[E82:F82].Copy [E77:F77]
[E86:F93].Copy [E87:F94]
[E94:F94].Copy [E86:F86]
[E98:F119].Copy [E99:F120]
[E120:F120].Copy [E98:F98]
[E126:F153].Copy [E127:F154]
[E154:F154].Copy [E126:F126]
[E158:F179].Copy [E159:F180]
[E180:F180].Copy [E158:F158]
[E184:F187].Copy [E185:F188]
[E188:F188].Copy [E184:F184]
[E192:F195].Copy [E193:F196]
[E196:F196].Copy [E192:F192]
[BE8:BG200,A8:B200,H8:H93,H98:H119,H126:H200,E36:F36,E64:F64,E73:F73,E82:F82,E94:F94,E120:F120,E154:F154,E180:F180,E188:F188,E196:F196].ClearContents
[BE7:BG7].AutoFill Destination:=[BE7:BG200], Type:=xlFillDefault
Range("BE98:BE119").FormulaR1C1 = "=RC[-51]"
Range("D98:D119").Formula = "=INDEX(DR$98:DS$119,MATCH(F98,DR$98:DR$119,0),2)"
[A7:B7].AutoFill Destination:=[A7:B200], Type:=xlFillDefault
With Range("G8")
        .FormulaArray = myFormulaPart1
        .Replace "X_X_X))", myFormulaPart2
        .Replace "Y_Y_Y", myFormulaPart3
    End With
    
    Range("G8").Copy
    Range("G9:G35,G40:G63,G68:G72,G77:G81,G86:G93,G126:G153,G158:G179,G184:G187,G192:G195").PasteSpecial xlPasteFormulas
    
    Range("H8:H35,H40:H63,H68:H72,H77:H81,H86:H93,H126:H153,H158:H179,H184:H187,H192:H195").Formula = "=IF(A8<>$A$5,"""",INDEX($F$98:$F$119,MATCH(C8,$H$98:$H$119,0)))"
[BD:DJ].EntireColumn.Hidden = True
[E:E].EntireColumn.Hidden = True
[A:B].EntireColumn.Hidden = True
[H99] = "Mandeville Hall School"
[H98] = "Find Work"
[H98].Copy [H100:H119]
[H3].Select
ActiveWorkbook.SaveAs Filename:="E:\Ali Land\MrExcel\Footscray Roster\FSCY " & Format(Range("BC1").Value, "yy-mm-dd") & ".xls"
End Sub

Asad

 

[asad]

Bump.