Replacing a sheet in an excel workbook

Reply
Occasional Contributor
Posts: 11

Replacing a sheet in an excel workbook

I'm trying to fix someone else's code that replaces a sheet in an excel workbook. Her first step is to use something like this  :

libname fpr excel "\\INTLfs\files\test.xlsm"
Unfortunately, this does not work because SAS thinks the file is open: hence:

          ERROR: Connect: Cannot update. Database or object is read-only.
         ERROR: Error in the LIBNAME statement.

So I've been looking around for ways that people have forced the file to close,  Someone came up with this macro:


%macro dde_close_without_save();
filename cmdexcel dde 'excel|system';
data _null_;
file cmdexcel;
put '[close(0)]';
run;
filename cmdexcel clear;
%mend;
%dde_close_without_save;

 

Unfortunately, the result is

ERROR: Physical file does not exist, excel|system.

 

I just need something that can force a file to close so that I can then open and update it.

Please help

Occasional Contributor
Posts: 11

Re: Replacing a sheet in an excel workbook

 

This might be helpful but I cannot get something like this to work. Someone used this code to open an excel document:

 

filename DDEcmds dde 'excel | system';
data _null_; /* talk to DDE, no output data */
file DDEcmds;
put '[open("L:\\SummaryReport.xls")]'; /*Location and name of file to open*/
put '[SAVE()]';
put '[QUIT()]';
run;

 

Someone else said the key to the problem was to use this:

put '[close(1,"Workbook.xls")]' 

(Where 1 is to save changes, and 0 would be to not save changes.)

 

But I cannot figure out some way to make a combination of these work in my situation. 

Trusted Advisor
Posts: 1,116

Re: Replacing a sheet in an excel workbook

Hello Tim,

 

A communication between SAS and Excel via DDE is possible only if both programs run on the same machine. For example, I have SAS on a workstation and Excel on a different PC in the network, hence no chance to use DDE for a connection. (The log message when I try is the same as you mentioned: ERROR: Physical file does not exist, excel|system.)

 

Unfortunately, I don't have a license for "SAS/ACCESS Interface to PC Files" either, so I can't test anything involving the "EXCEL engine," which would be used in a statement like "libname xxxxx excel ...".

 

 

Occasional Contributor
Posts: 11

Re: Replacing a sheet in an excel workbook

Freelance,

 

Thanks for the info.  That might be the issue. Or at least part of it.  My coworker has been working remotely for this step.  It used to work for her but then stopped. Perhaps she started running the code from the wrong place. She borrowed the code from someone else and it is likely that she doesn't know about what you have explained.  On Monday, I'll have to see how she is running it.  

Thanks

Super User
Posts: 19,151

Re: Replacing a sheet in an excel workbook

Maybe a different solution is warranted?

 

Copy the original file, add your sheet and save as a new version. Move file to folder where only recent file is stored. This way you also have historical versions if required. 

 

 

 

Super User
Posts: 3,235

Re: Replacing a sheet in an excel workbook

Here is a useful reference comparing various Excel LIBNAME options:

 

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#p05hxa7xihwolmn1sd...

 

In theory what you are trying should work - are your using SAS 9.4? If you are on M2 or higher you could try XLSX but your workbook would have to be that type too.

 

Are you sure you have write access in the location you are testing and the TEST workbook doesn't have the read-only flag set?

Respected Advisor
Posts: 4,816

Re: Replacing a sheet in an excel workbook

Can you open and update the file with Excel, from the same location where SAS is running?

PG
Occasional Contributor
Posts: 11

Re: Replacing a sheet in an excel workbook

When I try to open it in Excel, sometimes it says it is locked for editing though no-one has it open. I hoped that I could fix that by closing it in SAS.

Super User
Posts: 19,151

Re: Replacing a sheet in an excel workbook

@TimWright That sounds like a system issue, not a SAS issue. 

http://answers.microsoft.com/en-us/office/forum/office_2013_release-excel/excel-file-locked-for-edit...

 

There's some solutions in link above. I've also seen this when the file is being scanned by virus checker. 

 

 

Occasional Contributor
Posts: 11

Re: Replacing a sheet in an excel workbook

Reeza has it right in that the problem is not a problem caused by SAS. I realize now that the issue is more basic. The computer thinks someone else has it open so there is nothing SAS can do. I hoped that I would be able to close the file in SAS, but since it is already supposedly open by another user, SAS cannot just close it the way I hoped. Unfortunately, I cannot find any way to get the status back to "closed" for that file. I'll have to copy something there under a different name, but it looks like I won't ever be able to name it what it was.  Thanks for your suggestions, folks

Super User
Posts: 19,151

Re: Replacing a sheet in an excel workbook

Did you try the solution in the thread I posted, deleting the file in the temp location?

Except you may not know who has it open I supposed Smiley Sad

 

If you have admin rights, can you force delete the file? Copy the file, update, delete old file and then rename the new one?

 

 

Occasional Contributor
Posts: 11

Re: Replacing a sheet in an excel workbook

Thanks for showing me the link to a thread about a similar issue someone else was having.  Unfortunately I do not have admin rights and I could not find the files that they describe.  I am accessing these files and SAS code remotely, so there isn't much I can do.  I think the issues is that I have accessed it remote, and so has someone else.  It may be that something one of us did didn't cause it to close properly. It doesn't look like I will be able to replace that file because no one seems to have authority to delete it (because it ithinks it is open by someone)

[sigh]

Occasional Contributor
Posts: 11

Re: Replacing a sheet in an excel workbook

UPDATE:

 

By doing 

TOOLS -> Folder options ->[ View ] and unchecking the option to Hide protectected operating system files, I was able to see the temporaty ownership files that indicate the file is open.

 

for example, when I had test1.xlsm open, I could see in the list ~test1.xlsm.  So now I know that at least if I am the one that has ownership, and it thinks it is open when it is not, I can delete ~test1.xlsm. At least, thats what I think they are saying.  So far so good.

 

The next thing I did was to delete or remove the file that is supposedly read only, out of that subdirectory.  That worked.

 

(3) now I put my properlly functioning file, Test1.xlsm, in the target subdirectory.

(4) Opening SAS, I used " libname fpr excel "\\INTLfs\files\Nepal\DataOps\Reports\CIDI2016\FPR\test1.xlsm" ; to target the file. This worked.

(5) I got out of SAS. Then went and renamed test1.slsm to the name of the file that was previously lock for read-only: Nepal_CIDI_FPR_tsk.xlsm.

 (6) I went back into SAS and libname fpr excel "\\INTLfs\files\Nepal\DataOps\Reports\CIDI2016\FPR\Nepal_CIDI_FPR_tsk.xlsm." fails.  

 

It seems like SAS has somehow got this name stored somewhere as being read only, even though that file has been deleted and replaced by a file that works.

 

Therefore, once again it sounds like it is a SAS issue. Does this mean I cannot use that file name anymore?

 

Ask a Question
Discussion stats
  • 12 replies
  • 577 views
  • 3 likes
  • 5 in conversation