How to Lock or Periodically Update Source File Links

[dude47]

Ok, I have spent the better part of 5 hours googling everywhere and I can't seem to find an answer to this. I am not new to excel but I am new to linking between workbooks and anything above basic formulas. I have Workbook A that I am linking cells to in Workbook B which is being used as a Display for tracking information in workbook A on a TV Screen in an office. So when the cell is updated in Workbook A it updates in Workbook B. I used the copy and paste link function and it worked just fine. I update the cell in Workbook A and the associated cell in Workbook B updates just like I want.

Here is where the problem comes in. The link is not locked and Workbook A has to be saved as a backup with the name changed to have the date and time in it about 20 times a day. So the link will have the reference for the working version of Workbook A (which always stays in the same file location) but when you "Save As" and change the name to the "Date Time Workbook A" The source name in workbook B's link automatically changes to "Date Time Workbook A". Then if you close it and Opened the Original working copy of Workbook A nothing update because the links are now all looking at "Date Time Workbook A".

I know a work around it is "Save As" "Date Workbook A" then re "Save As" original "Workbook A". If it were just me that would be easy and I would do that but there are dozen of people that are going to be changing numbers in the working version of "Workbook A" then saving date and time stamped copies that trying to teach everyone this and getting them all to do it will be like herding cats.

So what I need is a way of locking the link for "Workbook A" in "Workbook B" so no matter what happens to "Workbook A" "Workbook B" will always look for the original linked filename.

Please help I am ripping my hair out over this. I am sick of getting called and told "The Status Board isn't updating again".

What I am thinking is having a VBA code running int he background automatically that takes any file link path containing words from the saved backup file and replaces it with the original file link path.

For Example

If the working copy link is "C:\Users\Public\Status\Widget Status"

And the file backups are saved as "C:\Users\Public\Status\BackUps\20170101 Widget Status 0930AM" (This is what it keeps changing it to automatically)

I need a VBA code that will If the link path includes "Widget Status" ("C:\Users\Public\Status\BackUps\20170101 Widget Status 0930AM" for example) then replaces it with the original "C:\Users\Public\Status\Widget Status". So no mater what the link path changes to if it contains key words it still looks at the original file.

I would need it to be constantly running it in the background as the file path is automatically updated by by excel when the original file path is changed it would change it back to the original.

Is this possible?



Thanks,
The Dude

 

[jorismoerings]

Re: How to Lock Source File Links

Hi and welcome to MrExcel,

There is no "true" locking mechanism but there's a work-a-round.
Assuming the cell in workbook B is A1 and has something in it like ='[Workbook A]sheet 1'!A1
Change that to: -INDIRECT("'[Workbook A]sheet 1'!$A$1")

Check before implementing if the sheets names are correct.

 

[dude47]

Re: How to Lock Source File Links

I tried this but it does not seem to be working. The Formula has a macro reference in the front of it. I wish I could copy and paste it but the computers it is running on does not have internet access.

 

[svendiamond]

Re: Need help with VBA code to constantly update and change link path

Does the link path always end in a backslash? Like is it actually:

"C:\Users\Public\Status\BackUps\20170101 Widget Status 0930AM"

or is it

"C:\Users\Public\Status\BackUps\20170101 Widget Status 0930AM"

 

[jorismoerings]

Re: How to Lock Source File Links

Hi, can you share one of the formula in workbook B?

 

[Macropod]

Re: How to Lock Source File Links

Dude47: You started two threads on the same topic. Although the focus was slightly different, both concern the same problem and are, to that extent, duplicates. I've merged them into one thread. Please read Forum Rule #12 (http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html) re duplicate threads.

 

[dude47]

Dude47: You started two threads on the same topic. Although the focus was slightly different, both concern the same problem and are, to that extent, duplicates. I've merged them into one thread. Please read Forum Rule #12 (http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html) re duplicate threads.

Sorry I figured I was trying to ask for 2 different things

Hi, can you share one of the formula in workbook B?

=Excel.SheetMacroEnabled.12|'C:\Users\Public\Status\BackUps\Widget Status.xlsm'!'!Sheet 1!R4C10:R5C10'

 

[jorismoerings]

Ok,

This raised the question why you need a MIME type in the Link. A Multi-Purpose Internet Mail Extension (eg. “application/vnd.ms-excel.sheet.macroenabled.12”), also known as a MIME, is type of Internet standard originally developed to allow the exchange of different types of data files through e-mail messages.

Do you actually need this as your question does mention you're linking field between 2 excel sheets?

If you don't need it:
change the formula to this: =INDIRECT("''C:\Users\Public\Status\BackUps\Widget Status.xlsm]Sheet 1'!J4")

 

[dude47]

Ok, I will try it again. I had two excel windows open with each workbook, I selected the cell I wanted in the first workbook and then right clicked on where I wanted it to go in the second workbook and selected paste link. That formula is the one that it automatically pasted. The version of excel is 2010 if that maters.

 

[dude47]

So I tried this last night on my personal computer. I created a workbookA and WorkbookB and used the INDIRECT function typed in the WorkbookA Sheet 1 and Cell A1 ref just like you did and it worked! I was so happy. I got to work today and tried it on the file with the correct workbook names and Sheet names and it gives me a #REF. I even tried to do the same thing and create a test workbookA and workbookB on the company computer just like I did on my personal computer and it would not work. It must be a setting issue somewhere. Also when I link a cell from one workbook to another on my personal computer it does not put the marco MIME link but it always puts one in on the company computer. Only thing I can think of is it is a setting issue in excel itself. All attempts were made with both workbooks in the same file.