Excel lookup - read only

Posted by: DougA

Excel lookup - read only - 12/14/14 01:28 PM

The excel lookup function is read-only but it opens the spreadsheet for writing. Could it be changed so it only opens it for read? I've had a number of issues where users have opened the spreadsheet for editing only to leave it open causing the workflow to fail.

Posted by: Raphael Lalonde Lefebvre

Re: Excel lookup - read only - 12/16/14 01:35 PM

Hi Doug,

I don't know exactly how the Excel Lookup plugin is coded, but generally, trying to open an Excel file that is already open will cause issues. Not just with this plugin either, even ODBC connections to Excel files will fail, so I don't know if it's even possible.

Even if it was, I don't think our devs will change the plugin anytime soon since it has been working this way for several years now, and there hasn't been any complaints about this until now. In the end, the cause of this is the bad practice of not closing documents. So instead of changing the software to accomodate a bad practice, I believe the best solution is to fix the bad practice, and have the users properly close their documents when they are done editing them.

Raphaƫl Lalonde Lefebvre
Posted by: DougA

Re: Excel lookup - read only - 12/29/14 01:14 PM

Thanks Raphael,
You're quite right, you find you can't open an Excel file that's already open... unless you open it read only. You can open a spreadsheet read only through ODBC as well.
In my experience it's better to only open files with the privileges neccesary. The Microsoft Workbook open method has a parameter for opening the file read only.
Try as I might to train our users I find they often forget or ignore instructions! Sadly we'll have to continue protecting our users from their own bad practices. I've written my own script to do open an excel file (read only of course!) and conduct a vlookup. For the sake of simplicity I return all of the cells as metadata using the column name as the name.
Thanks anyway

Posted by: Jonno

Re: Excel lookup - read only - 12/30/14 08:00 AM

Might be worth considering running some sort of script to copy the workbook somewhere prior to running and open the copied version instead. This version would be somewhere on the filesystem a user will never touch, and would allow full read/write access on it (the file can be copied elsewhere and used even if someone has it open).