Links stop updating in Excel 2007.
A workbook that was working perfectly in Excel 2003 starts failing in Excel 2007. For no apparent reason, the links will no longer update. Episode #1174 shows why and how to fix this problem forever.
A workbook that was working perfectly in Excel 2003 starts failing in Excel 2007. For no apparent reason, the links will no longer update. Episode #1174 shows why and how to fix this problem forever.
Transcript of the video:
MrExcel podcast is brought to you by ”Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Cathy, now Cathy had been one of my webinars, and I always say “Hey, if you ever have any weird, weird Excel things, shoot me a note, because I love weird Excel things!” Cathy recently upgraded from Excel 2003 to Excel 2007.
Back in Excel 2003, everything was working perfectly, of course, Excel 2007, none of these links are updating.
She found that she actually have to come in and press F2 and Enter for every single cell in order to get these to update, driving her insane.
“Why does Excel 2007 not support links?” she said.
Well, it's not a problem with Excel 2007 and links, it is a problem with the Security Center.
All of a sudden in Excel 2007, external links, which we've been using, what, for decades, are seen as a security risk, and there's a chance for those get turned off.
Not in Edit Links like we had before, it's even more insidious than that, so.
Boy, they really have this hidden it, this really should be a Where-Is-It-Wednesday!
We have to go to the File button, then, down to Excel Options, then to the Trust Center, then, on the right-hand side Trust Center Settings, then, over here External Content, check this out, Security Settings!
Security?
It's a link!
What's going to happen?
For Workbook links it's set to Disable.
Now that should not be the default, but in some companies, the IT department is using Group Policy to make that be the default.
So, at least prompt the user for automatic update, or if, you know where the data is coming from, this is not recommended, so I'm going to not recommend it but.
do what you need to do, and Prompt about Data Connections, there, alick OK, click OK! Alright, so now, let's try this again, we're going to save this, close it, and we'll come in.
What if we have to restart Excel for those settings to take ef- Ah, there it is, right there.
So, in Excel 2003 you weren't able to do anything, big box pops up(?), you had to choose “Do we update links or not update links?” You weren't allowed to actually start using the spreadsheet.
Now they move it here to info bar, not sure that I like that, I would rather have people be forced to answer this question.
But we can come here, and here the option to say “Yes, enable this content.” Click OK, and then everything will have the proper numbers.
Actually, personally, and I argued this with Microsoft, I think it is far more dangerous to just have that sitting up there in the info bar, and not have the links update.
People are going to be running around with the wrong numbers, that was, in my opinion, a bad, bad thing for them to do.
Well, not bad bad, just slightly mildly bad.
Even worse that they allow someone in Group Policy, someone over in IT who never uses Excel, to turn off Link Settings all together.
Cathy, I hope that you sent this to me within a couple of days, because if you've been sitting there pressing F2 and Enter on all those cells for more than a few days, I am so sorry!
Alright, so hey, want to thank Kathy for sending that question in, want to thank you for stopping by, see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent in by Cathy, now Cathy had been one of my webinars, and I always say “Hey, if you ever have any weird, weird Excel things, shoot me a note, because I love weird Excel things!” Cathy recently upgraded from Excel 2003 to Excel 2007.
Back in Excel 2003, everything was working perfectly, of course, Excel 2007, none of these links are updating.
She found that she actually have to come in and press F2 and Enter for every single cell in order to get these to update, driving her insane.
“Why does Excel 2007 not support links?” she said.
Well, it's not a problem with Excel 2007 and links, it is a problem with the Security Center.
All of a sudden in Excel 2007, external links, which we've been using, what, for decades, are seen as a security risk, and there's a chance for those get turned off.
Not in Edit Links like we had before, it's even more insidious than that, so.
Boy, they really have this hidden it, this really should be a Where-Is-It-Wednesday!
We have to go to the File button, then, down to Excel Options, then to the Trust Center, then, on the right-hand side Trust Center Settings, then, over here External Content, check this out, Security Settings!
Security?
It's a link!
What's going to happen?
For Workbook links it's set to Disable.
Now that should not be the default, but in some companies, the IT department is using Group Policy to make that be the default.
So, at least prompt the user for automatic update, or if, you know where the data is coming from, this is not recommended, so I'm going to not recommend it but.
do what you need to do, and Prompt about Data Connections, there, alick OK, click OK! Alright, so now, let's try this again, we're going to save this, close it, and we'll come in.
What if we have to restart Excel for those settings to take ef- Ah, there it is, right there.
So, in Excel 2003 you weren't able to do anything, big box pops up(?), you had to choose “Do we update links or not update links?” You weren't allowed to actually start using the spreadsheet.
Now they move it here to info bar, not sure that I like that, I would rather have people be forced to answer this question.
But we can come here, and here the option to say “Yes, enable this content.” Click OK, and then everything will have the proper numbers.
Actually, personally, and I argued this with Microsoft, I think it is far more dangerous to just have that sitting up there in the info bar, and not have the links update.
People are going to be running around with the wrong numbers, that was, in my opinion, a bad, bad thing for them to do.
Well, not bad bad, just slightly mildly bad.
Even worse that they allow someone in Group Policy, someone over in IT who never uses Excel, to turn off Link Settings all together.
Cathy, I hope that you sent this to me within a couple of days, because if you've been sitting there pressing F2 and Enter on all those cells for more than a few days, I am so sorry!
Alright, so hey, want to thank Kathy for sending that question in, want to thank you for stopping by, see you next time for another netcast from MrExcel!
