fcopy function failed when the file is opened.

[whymath]

I am using fcopy() function to copy an Excel file, here is the code:

filename spec "spec.xlsx" recfmt=n;
filename speccopy "%sysfunc(pathname(work))\spec.xlsx" recfmt=n;

data _null_;
  rc=fcopy('spec','speccopy');
  if rc>0 then do;
    msg=sysmsg();
    put msg=;
  end;
run;

It works usually, but failed while I am editing this Excel file, how to make fcopy() just make a copy of the Excel file while the file is on editing?

[Tom]

Talk to Microsoft about changing the behavior of Windows?

[Quentin]

Looks like fcopy() is happy to copy an Excel file while the file is open.  Also copies fine if the file is open and there are unsaved changes to the file.  But it would make sense that when you hit SAVE in Excel, at some point in that process when it writes the file to disk, Excel is going to get an exclusive lock on the file.

If your timing is occasionally unlucky enough that you are trying to copy the file while someone is writing to the file, I think the best you can do is detect the problem and try again.

[whymath]

Maybe your OS is Linux? SAS behaves oppsite on my Windows Server OS.

[Quentin]

I tested on local Windows with PC SAS. With the Excel file open, I could copy it using Windows file explorer and could copy it using copy(). But could be different for Windows server.

[whymath]

Tried PC SAS, still doesn't work. This is the screeshot:

What's your enviroment?

[SASKiwi]

This is a feature of Windows and Excel. Any attempt to read an Excel file in SAS under Windows will fail if it is already opened in Excel. FCOPY, PROC IMPORT, the EXCEL or XLSX LIBNAME engines will all fail. 

[whymath]

Thank you guys.

Finnally, I use x command to replace fcopy function.

x copy "spec.xlsx" "%sysfunc(pathname(work))";

Still waitting for SAS solution.

[LinusH]

Placing office files in SharePoint and in Teams let you edit the file simultaneously, perhaps this also works for SAS access?
Haven't tried since my SAS server can't access those due to firewall rules.

[Sajid01]

Hello

My understanding is that SAS should hold an exclusive copy for fcopy to work.

[Kurt_Bremser · Accepted solution]

SAS uses locking to prevent that a file is changed while FCOPY is in progress (keep in mind that FCOPY can be used with file references pointing to remote locations, so the copy could take quite some time and the result be inconsistent). Since Excel keeps an open handle on a file, FCOPY fails because the lock fails.

Your method of using an external command is the proper workaround. For documentation in the SAS log, you should use PIPE to run the external command:

filename oscmd pipe "copy 'spec.xlsx' '%sysfunc(pathname(work))' 2>&1";

data _null_;
infile oscmd;
input;
put _infile_;
run;