External Links in Excel 2007

[betany70]

Hi Folks -
We are using external links to create a grading sheet for instructors to
populate student names, dates, other text data. (Our school's database
exports this information into a csv file based on a query) Most of our staff
neither knows nor cares how it works - unfortunately I am having a heck of a
time getting the links to update without opening the source file and fear
that staff will become frustrated and not use the tool.

1. I get the Security Warning in the status bar stating that Automatic
update of links has been disabled. Once I clear that

2. I get a message stating: Excel cannot update one or more links in this
workbook. To update the links, open all the link source files and press F9
to update calculations

Is there a way to disable this and update the links automatically, and will
it make our network admins crazy (meaning does it leave the door open to
bigger problems).

Thanks!!!!!!

 

[Harlan Grove]

We are using external links to create a grading sheet for instructors to
populate student names, dates, other text data. (Our school's database
exports this information into a csv file based on a query) . . .
. . .getting the links to update without opening the source file . . .

....

Do you mean you've designed an Excel workbook (.XLS[XMB] file format) with
external links into CSV files? If so, no way to fix this without using VBA,
and that could lead to more security issues. Excel can only fetch values
from closed files when those closed files are in one of the .XLS[XMB]
formats. Excel can't fetch values from closed files of any other type even
if it could if those files were open.

The VBA/udf alternative involves opening the files, parsing them line by
line, so logical row by row, and returning the specified cell(s). But if
you're going to use VBA anyway, you'd be better off importing CSV files into
worksheets in your .XLS[XMB] file (using whatever Excel 2007's equivalent
for Excel 2003's Data > Import External Data > Import Data > . . .) and
addressing it as an INTERNAL range. Alternatively, convert the CSV files to
..XLSX format, then change the external references in your .XLS[XMB] file to
refer to the .XLS files rather than the .CSV files.

 

[betany70]

Thanks for the bad news Will probabaly convert the file to xls - thanks!

We are using external links to create a grading sheet for instructors to
populate student names, dates, other text data. (Our school's database
exports this information into a csv file based on a query) . . .
. . .getting the links to update without opening the source file . . .

....

Do you mean you've designed an Excel workbook (.XLS[XMB] file format) with
external links into CSV files? If so, no way to fix this without using VBA,
and that could lead to more security issues. Excel can only fetch values
from closed files when those closed files are in one of the .XLS[XMB]
formats. Excel can't fetch values from closed files of any other type even
if it could if those files were open.

The VBA/udf alternative involves opening the files, parsing them line by
line, so logical row by row, and returning the specified cell(s). But if
you're going to use VBA anyway, you'd be better off importing CSV files into
worksheets in your .XLS[XMB] file (using whatever Excel 2007's equivalent
for Excel 2003's Data > Import External Data > Import Data > . . .) and
addressing it as an INTERNAL range. Alternatively, convert the CSV files to
..XLSX format, then change the external references in your .XLS[XMB] file to
refer to the .XLS files rather than the .CSV files.