Macro to update links from file with password protection

andyoungy

New member
Joined
Jul 18, 2014
Messages
6
Reaction score
0
Points
0
I am completely new to Marcos but due to some great advice in another post on the forum I am trying to get a macro to update links I have in a workbook that is linked to another excel file sat on a network drive but it has a password protection!


I have recorded the marco opening the 'edit links' and then clicking update values, I add the password for the excel file the links are reading from and stop the macro. When I run the macro afterwards it still comes up asking for the password again?


Any help?


The overall plan is that I have 36 files around my business and they all read from sections of a 'master' file. The master file must be password protected so everyone can't see the whole master document! This is the closest I have got to getting it to work but I can't get past this password issue when running the macro.
 

andyoungy

New member
Joined
Jul 18, 2014
Messages
6
Reaction score
0
Points
0
So share it and we may be able to tweak…

Hi, it is a very basic Macro which goes to the other protected file, I recorded it as I went to the file, added the password and refreshed the links. This was my first ever Macro! However, it still comes up and asks for the password to refresh the data every time.

The protected file is password protected to get into it, something set up in tools when saving the file to our network drive so not sure if there is a better way to protect entry so this can still work, but I need to make sure no one can get into the master file when these are reading from, only the one person with a password.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
So share it and we may be able to tweak…
 

andyoungy

New member
Joined
Jul 18, 2014
Messages
6
Reaction score
0
Points
0
So share it and we may be able to tweak…

Sorry I was certain I has pasted it into the thread:

Sub Macro1()
'
' Macro1 Macro
'


'
ActiveWorkbook.UpdateLink Name:= _
"R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\Artwork Allocated.xls" _
, Type:=xlExcelLinks
End Sub

Is that what you need?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
UpdateLink has no way to supply a password, there might be a work-around, but that involves briefly opening the file, and if someone is clever they might be able to stop the code and see the newly opened workbook, or look at the code (even if it's password protected!) and see the password of the Artwork Allocated.xls.
Anyway, it goes something like:
Code:
Sub blah()
'On Error GoTo here
Application.ScreenUpdating = False 'hides most of the activity of opening/closing a file.
Set xxx = Workbooks.Open(Filename:="R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\Artwork Allocated.xls", Password:="a") 'adjust the password!
'the next Calculate line may be needed depending on the Calculation option you have set for your workbook (if it's set to [I]Manual Calculate[/I]), remove the apostrophe if that is the case:
'Calculate
xxx.Close False
here:
Application.ScreenUpdating = True
End Sub
 

andyoungy

New member
Joined
Jul 18, 2014
Messages
6
Reaction score
0
Points
0
UpdateLink has no way to supply a password, there might be a work-around, but that involves briefly opening the file, and if someone is clever they might be able to stop the code and see the newly opened workbook, or look at the code (even if it's password protected!) and see the password of the Artwork Allocated.xls.
Anyway, it goes something like:
Code:
Sub blah()
'On Error GoTo here
Application.ScreenUpdating = False 'hides most of the activity of opening/closing a file.
Set xxx = Workbooks.Open(Filename:="R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\Artwork Allocated.xls", Password:="a") 'adjust the password!
'the next Calculate line may be needed depending on the Calculation option you have set for your workbook (if it's set to [I]Manual Calculate[/I]), remove the apostrophe if that is the case:
'Calculate
xxx.Close False
here:
Application.ScreenUpdating = True
End Sub

Looks great, thank you for that I will give it a try, which bits do I need to delete or will the code work with your notes added?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
In any event, the apostrophe needs removing from:
'On Error GoTo here
(my error for leaving it in)

The other single line you may need, if updating doesn't happen, is:
'Calculate
At the moment it is inoperative. Removal of the apostrophe at the beginning of that line activates it.
 

andyoungy

New member
Joined
Jul 18, 2014
Messages
6
Reaction score
0
Points
0
Almost there, thank you! The one last thing is it comes up with another password box, this time with read only button, when I click read only it works great but is there a way to make the macro do that automatically?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,200
Reaction score
16
Points
38
Excel Version(s)
365
at a guess, change to:
Set xxx = Workbooks.Open(Filename:="R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\Artwork Allocated.xls", ReadOnly:=True, Password:="a")
 

andyoungy

New member
Joined
Jul 18, 2014
Messages
6
Reaction score
0
Points
0
Wow it looks like it has worked! Thank you so much, I need to do some testing with it as we have a variation of office products and different hardware issues but it should be ok! Thank you again, you have been a great help
 
Top