Macro to update links from file with password protection
I am completely new to Macros 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 macro 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?
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.
Could you post the code, or at least the portion of it where you are setting up communication with the other (protected) file and trying to update the links so we can see what you have right now?
Now - to confirm: if you were to try to open the other file, you would have to provide a password just to open it by itself in Excel?
It may be that SendKeys() may be needed to do this. Not sure without seeing your code and testing, and verifying whether the protection is at the file, workbook, or worksheet level.
Sorry for the delay, thank you for getting back to me, I am still having the problem getting the information from the protected file (sat on a network drive) to the 'report' I am looking to populate. I have tried a connection but the most reliable I have
found (without the protection) is links through the formula, but it is a very basic formula to read the other sheets cell:
='R:\Manufacturing and Operations\Stock & Priorities\Lecia - Artwork Allocated\[Artwork Allocated.xls]Glasgow'!F2
The 'Artwork Allocated' sheet is the protected sheet, I need to make sure no one in the business, other than one person, can access of edit this master sheet as they will all have their own reports which will read just their specific information through
the link or connection. The formula above is great until I password protect the whole Artwork Allocated file as it is asking for the password to bring in the date and the macro I tried still needs the password to access the date in there!
See if this code gives you any ideas on how to handle it. It will open a workBOOK (a .xls, .xlsx, .xlsm etc. file) that requires a password to open and then it copies data from one of the sheets in it to a sheet in the workbook with the code:
Dim protectedWB As Workbook
'for speed and so that user does not see the other
'workbook/worksheet while it is open
Application.ScreenUpdating = False
'you need to provide the full path and filename of the workbook to be opened
'and provide its password as part of that .Open command:
Set protectedWB = _
Workbooks.Open(Filename:="L:\TestFiles\Artwork Allocated.xls", Password:="MyWord")
'back to this workbook to help prevent seeing the other
'copy the entry on sheet Glasgow at F2 into Sheet1!A1 of this workbook
'specify that we want to copy the information into a sheet in this book.
ThisWorkbook.Worksheets("Sheet1").Range("A1") = protectedWB.Worksheets("Glasgow").Range("F2")
'close the protected workbook without saving any changes that may have
'been made due to recalculation or anything else.
protectedWB.Close False ' close without saving changes
'release the resource back to the system for future use
Set protectedWB = Nothing
I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein