DATA Step, Macro, Functions and more

DDE to close a particular excel workbook

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

DDE to close a particular excel workbook

Hi,

I'm trying to create DDE to close a particular excel workbook which is currently open. The workbook name needs to be passed as a macro variable.

Does anyone know how to do this? Below is what I've tried but I'm getting the error: "DDE syntax error in physical name"

Thanks in advance for any advice!

%let filenm = c:\temp\file.xls;

filename DDEcmds dde "Excel|&filenm.";

data _null_;

    file DDEcmds;

    put '[close(true)]';

run;


Accepted Solutions
Solution
‎09-14-2013 10:42 AM
Occasional Contributor
Posts: 12

Re: DDE to close a particular excel workbook

Hi,

Thanks again for your help.

I finally found the solution, and of course, it's one of those ones that is easy when you know how...

All that was needed was:

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

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

By the way Reeza, you caught my eye with the line "use DOCUMENTS or WINDOWS to get the list of all open documents". Where can I use the DOCUMENTS or WINDOWS commands? I've never heard of those before...

View solution in original post


All Replies
Super Contributor
Posts: 339

Re: DDE to close a particular excel workbook

Hi Js,

Based on SAS documentation, it appears as though if you use paths in your dde triplet, they need to be quoted within the quote

so

%let filenm = "c:\temp\file.xls";

filename DDEcmds dde 'Excel|&filenm.';

should resolve your current error.

Otherwise, you could consider using the blank workbook approach and use commands to open/modify/save/close the workbook within your data null statement

filename DDEcmds dde 'excel|system';

Super User
Posts: 19,785

Re: DDE to close a particular excel workbook

Posted in reply to Vince28_Statcan

I don't know if the macro value will resolve in single quotes.

This is the code I use to open /close excel files using macro variables:

options noxwait noxsync;

x '"C:\Program Files\Microsoft Office\Office14\excel.exe"';

data _null_;

    z=sleep(1); /* wait 1 seconds for Excel to start */

run;

filename cmds dde 'excel|system';

data _null_; /*get DDE to open Excel file for this run, no output data */

    file cmds;

    put "[open(""&output_path.\&file_name..xlsm"")]";

    x=sleep(1); /* wait 1 seconds for it to open */

run;

data _null_;

    file cmds;

    put '[close(0)]';

    put '[quit()]';

run;

Super Contributor
Posts: 339

Re: DDE to close a particular excel workbook

Indeed the macro variable would not resolve. My bad. You can just swap quotes and double quotes and it would resolve properly

Occasional Contributor
Posts: 12

Re: DDE to close a particular excel workbook

Thanks a lot guys.

They are helpful comments. However, my issue is a little more intricate. The excel workbook that I would like to close has already been opened by another process, and therefore if I use the OPEN command, I get a message saying the file is already open.

That's why I'm looking for some other way to point to that Excel workbook and then send the CLOSE command.

Any ideas?

Super User
Posts: 19,785

Re: DDE to close a particular excel workbook

I think you need to explain the situation some more. How are you opening the workbooks, what process opens them? Do you have multiple workbook opened?

Super Contributor
Posts: 339

Re: DDE to close a particular excel workbook


I'm with Reeza, I'd like to see more of the code to figure out ways around.

As an alternative to test, you could strip the name of your workbook from the file path and use

activate command to get the targetted file.xls as the current active workbook and then close it.

Like

%let wbname='file.xls';

put "ACTIVATE("&wbname.")";

This is all untested, I'm just reading through the no longer supported DDE documentation from microsoft.

Occasional Contributor
Posts: 12

Re: DDE to close a particular excel workbook

Hi Reeza,

The situation is like this...

The workbook is opened by MACRO1. The workbook name is saved to a macro variable by MACRO1.

Then, some time later, MACRO2 is run, and part of this macro's tasks are to shut the workbook that was opened by MACRO1.

And yes, unfortunately there are multiple workbooks open. It would have been better if this wasn't the case because then I could have used a simple CLOSE command without having to specify the file name.

Tricky one, isn't it! ;-)

Super User
Posts: 19,785

Re: DDE to close a particular excel workbook


Was this the method used to open the excel file?


filename DDEcmds dde "Excel|&filenm.";


If so, can you change the line to have a unique DDECMDS you can reference the filename in the macro2 to close it properly. 


filename &macr1_&filenm dde "Excel|&filenm.";


Super User
Posts: 19,785

Re: DDE to close a particular excel workbook

I'm assuming there is a timing issue though, you don't want to close anything thats still in progress. 

My recommended solution, or at least what I did when faced with this issue, was to reorder my process such that I didn't have multiple files open at the same time.  It does add on to the running time, but there isn't a lot of error catching with the DDE process so if something goes wrong its hard to tell sometimes.

You can also leave them all open and then use DOCUMENTS or WINDOWS to get the list of all open documents and close all at once.

Solution
‎09-14-2013 10:42 AM
Occasional Contributor
Posts: 12

Re: DDE to close a particular excel workbook

Hi,

Thanks again for your help.

I finally found the solution, and of course, it's one of those ones that is easy when you know how...

All that was needed was:

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

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

By the way Reeza, you caught my eye with the line "use DOCUMENTS or WINDOWS to get the list of all open documents". Where can I use the DOCUMENTS or WINDOWS commands? I've never heard of those before...

Super Contributor
Posts: 339

Re: DDE to close a particular excel workbook

closeall.jpeg

documents.jpg

windows.jpg

Hopefully the images are viewable.

The one thing with leaving them all open and using documents or windows commands to close is that you would have needed to save them all before. With that said, if you did, you wouldn't really need the documents and windows command as you could simply use close.all command.

If you want that small APP for DDE, hit

http://support.microsoft.com/kb/128185/en-us

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 6666 views
  • 6 likes
  • 3 in conversation