How to avoid reading any .xlsm file when it is open while using Directory tool?

sachinshrivastava1981

Dear Community,

I need to know how can I avoid reading a .xlsm file when it is open so that workflow run without error like "The process cannot access the file because it is being used by another process". My files .xlsm are shared on a Shared Drive, Hence it can be open by anyone at any time.

Note: I am using Directory Input tool to read files from a folder. And file extension is .xlsm

But I want to find a solution when the workflow initiates, if avoids any file which is open and continues reading files which are closed.

Thanks in advance.

Error Message

Accepted answers

gawa

@sachinshrivastava1981 Alteryx tries to get LOCK of that file when WF runs, but fails if that file is already locked by another process. A far as I remember, in the older version of Designer(around 2020.3), it showed no error even if the target Excel is opened by another process but it does in the recent version. FYI.

So did you solve your original question? As @alexnajm says, using Batch Macro is the perfect solution to your case.

Assume reading total 3 files by Batch Macro. Even if file2 is opened, it still continue to read file3 though it shows the error: It reads file1 & file3 as if it skips reading file2.

On the other hand, if you use only Input tool as usual, it stops reading files once it fails to read any file: It reads file1, and stops.

All comments

alexnajm

A batch macro to read in multiple files should do the trick - here’s an example of one you can start with and adjust: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

sachinshrivastava1981

Problem is to avoid reading file when it is open. Batch Macro fails if the input file is open in someone machine. with error file is being used in other process.

alexnajm

The batch macro would show an error for that file, but it should still read in data from all of the other files. I have done this recently with corrupted files and it works like a charm

sachinshrivastava1981

Question: How does Alteryx understand if the file is Open or used in other process? Is that a property or attribute?

gawa

So did you solve your original question? As @alexnajm says, using Batch Macro is the perfect solution to your case.

Assume reading total 3 files by Batch Macro. Even if file2 is opened, it still continue to read file3 though it shows the error: It reads file1 & file3 as if it skips reading file2.

On the other hand, if you use only Input tool as usual, it stops reading files once it fails to read any file: It reads file1, and stops.

caltang

Although the batch macro fails, it still suceeds for other files. "Fails" in this case means it won't be read, I believe this fits your case.

But if you're asking how Alteryx determines that - if your file is open or even if it's a hosted file on SharePoint/OneDrive/SharedDrive, if someone has it open, the system will still consider that open and won't execute.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703