Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Macro to update links from file with password protection

  1. #1

    Macro to update links from file with password protection



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by andyoungy View Post
    I have recorded the marco…
    So share it and we may be able to tweak…

  3. #3
    Quote Originally Posted by p45cal View Post
    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.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    So share it and we may be able to tweak…

  5. #5
    Quote Originally Posted by p45cal View Post
    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?

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    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 Manual Calculate), remove the apostrophe if that is the case:
    'Calculate
    xxx.Close False
    here:
    Application.ScreenUpdating = True
    End Sub

  7. #7
    Quote Originally Posted by p45cal View Post
    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 Manual Calculate), 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?

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    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.

  9. #9
    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?

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    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")

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •