BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
js8765
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
js8765
Calcite | Level 5

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

11 REPLIES 11
Vince28_Statcan
Quartz | Level 8

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';

Reeza
Super User

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;

Vince28_Statcan
Quartz | Level 8

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

js8765
Calcite | Level 5

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?

Reeza
Super User

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?

Vince28_Statcan
Quartz | Level 8


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.

js8765
Calcite | Level 5

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! 😉

Reeza
Super User


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.";


Reeza
Super User

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.

js8765
Calcite | Level 5

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...

Vince28_Statcan
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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