Import a locked excel file

[Ullsokk]

I have a batch that will read data from an excel file on a daily basis, and run the programs whose filepath is included in the file. This will make it easier to add new programs to the batch, and keep track of which programs have a batch job. 

Few people have access to the excel-file, and getting a lock would be quite rare, as the batch runs outside of working hours. Still, I was wondering if there is a way to read the file even if the excel file is opened by someone. There doesn't seem to be a sas-spesific way to do this, but I can sort of imagine how to write this as a shell script that either runs is the file has a lock, as a X-command, or just writing a standalone shellscprit that either forces a close or makes a copy, so that the batch can import the file with the contents of the previously saved file. 

Is there a standard best practice for such situations? 

[Kurt_Bremser]

Don't keep such control data in a spreadsheet file, use a text file instead, and edit it with a text editor.

Notepad++ or the standard Windows Editor (just to give some examples) do not lock the file.

[Ullsokk]

That will probably not be an option in my case. In any case, I am planning to use a x-command to create a temporary copy of the file if it is already open. The only problem is getting SAS to check if the file is already open.

[AlanC]

https://stackoverflow.com/questions/24992681/powershell-check-if-a-file-is-locked

[andrewboyd]

Have you tried testing this to see what happens? I ask because as you aren't writing back to the excel file I can't see why there would be an issue as sas would presumably just get a read only version of the file just as a user would when they open a worksheet already opened by another user.

[Ullsokk · Accepted solution]

On second thought, I might as well just make a copy and read that file instead. No need to run a condition beforehand

[AlanC]

Write a Powershell script to kill the task.

[error_prone]

---

@AlanC wrote:

Write a Powershell script to kill the task.

---

Hardly possible to do in a Client-Server-Setup, the task locking the Excel-file will most likely not be running on the same machine as the sas process.

[AlanC]

Powershell can be executed remotely. However, I don't think we have enough informationas to the configuration of the system in question here.