Help re-writing macro

[Anotherdream]

Hello everyone. I have the following Macro (attached at the bottom) which works in most cases, however a user presented a problem to it that I don't know how to solve.   Basically I have a user who has a file located in a file path that has a single quote in it.  The file path looks like follows.  C:\company\brandon's files\.

So when they try to run the macro they enter.

%Mapping_Names("C:\company\brandon's files\MultiTabs.xlsm","MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")

This macro basically just runs A specific excel macro, and it works for all macros not found in a directory that contains a single quote.   I know the following section is the problem..

DATA _NULL_;

     FILE xcel;

     put  %unquote(%str(%'[open(&excelvariable)]%'));

     put  %unquote(%str(%'[run(&macrovariable)]%'));

     PUT "[quit()]";

RUN;

My "solution"

The only way I know how to fix this problem is to have the user enter two quotes in any of their calls to this macro (if their directory has a single quote in it)...  So their call would be...

%Mapping_Names("C:\company\brandon''s files\MultiTabs.xlsm","MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")

Obviously I could have the macro just scan through these variables and do a tranwrd to make the single '  into double '   so the user could enter whatever they want... HOWEVER is there a better way to do this?  I don't understand another way to fix this problem!

Thanks for your time!

%macro Excel_macro(excelvariable,macrovariable);

OPTIONS NOXWAIT NOXSYNC;

X '"C:\Program Files\Microsoft Office\Office14\EXCEL.EXE"';

DATA _NULL_;

     VAR1=SLEEP(1);

RUN;

  OPTIONS NOXWAIT XSYNC;

FILENAME xcel DDE "EXCEL|SYSTEM";

DATA _NULL_;

     VAR1=SLEEP(2);

RUN;

DATA _NULL_;

     FILE xcel;

     put  %unquote(%str(%'[open(&excelvariable)]%'));

     put  %unquote(%str(%'[run(&macrovariable)]%'));

     PUT "[quit()]";

RUN;

data _null_;

   VAR1=SLEEP(2);

RUN;

filename programs pipe "tasklist /svc";

data want;

  infile programs truncover;

  informat open_programs $80.;

  input open_programs;

  if open_programs eq "EXCEL.EXE" then output;

run;

%let dsid1=%sysfunc(open(want));

%let excelopen=%sysfunc(attrn(&dsid1,nlobs));

%let rc=%sysfunc(close(&dsid1));

%if &excelopen ~=0 %then %do;

  DATA _NULL_;

     FILE xcel;

     PUT '[quit()]';

RUN;

%end;

proc datasets;

delete Want;

run;

%mend Excel_macro;

[Community_Help]

Hi Anotherdream , I've moved your question to the Macro community here to increase visibility of your question. Thanks!

[Tom · Accepted solution]

What do you actually need to write to the Excel command file in this situation?

Since you already have a data step I would take all of the macro logic out of the problem.

Does this generate the right code?

%let excelvariable="C:\company\brandon's files\MultiTabs.xlsm";

%let macrovariable="MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV";

DATA _NULL_;

   FILE xcel;

   open = cats('[open(',symget('excelvariable'),')]');

   run = cats('[run(',symget('macrovariable'),')]');

   put open / run / '[quit()]' ;

RUN;

[open("C:\company\brandon's files\MultiTabs.xlsm")]

[run("MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")]

[quit()]

[RW9]

Just to add, your problem lies in the chap Brandon using special characters in the pathname.  Me personally, I would fix that rather than try to code round it.  You should have some sort of company policy on folder naming, e.g. only alphanumeric and underscores (instead of spaces).  It will make your life a lot easier.  Reason is if you fix it for one quote, next week someone else will turn up and want rob"myfolder"2's or something similar.  Stop the problem at source.

[Kurt_Bremser]

I strongly second what RW9 said.

[Anotherdream]

Hey RW9 and Kurt.  I STRONGLY agree with you that this is ideal, however I have no control over what other people in my company name their files.

I wish I did, but I sadly just don't.

Tom, your method worked great.... Can you explain to me why this works and what I have doesn't? I understand that symget grags the actual macro variable, howver since the macro varaible has a single quote in it, it seems to me like the program should have made it into a string, causing errors (since that's what happens in my code).

Why does calling it in Symget cause it to work, while the other does not?

Thanks!

[RW9]

Me personally I would have at minimum, an internal structure for your group which you control, and data which needs to be used would then come in via your group and be put onto your setup, otherwise your could be in problems.  I.e. what happens if they delete/rename, change folders, don't update to latest version of file etc.  If it was my process, I would write a document with an expectation of what files should be sent, what the structure of the files should be, delivery times etc.  My area would then be accessible only to me, and would be version controlled with a set import/validation phase to ensure the process is correctly implemented.  Plus I would want CSV data rather than Excel.

[Anotherdream]

Hello RW9. Again I agree with you, however I was tasked with helping other teams deal wit hthe messes of files that they have, using their existing folder structures.

We cant' change the folder structures because they have many other codes / applications / etc.. that point ot them.  SO that's pretty much out of the question.

I understand we could move the files to a different location, etc..  But in my opinion working around the problem doesn't help my understanding of the problem to begin with.

So I understand that it shouldn't be necesssary to solve the problem i'm trying to solve, BUT it can be (and since i'm coding a macro library, other users don't have to follow any kind of logical convention, so I want macros' that work in as many circumstances as possible).

That's why I asked for the help, if that makes sense!

[Tom]

It is mainly the difference in using macro coding to generate SAS code and just writing SAS code directly.

In your program you are trying to get the macro code to generate a statement like:

PUT 'string' ;

In my program I am using SAS code to generate a variable that has the value I want and then I use

PUT variable;

to write the value.

Since you tried to wrap the string in single quotes any embedded single quotes would need to be doubled to generate a valid string literal value that you could use in a PUT statement.

You could have instead used %SYSFUNC() to call the QUOTE() function.  This will generated a string literal quoted with double quote characters and any embedded double quote characters will be automatically doubled up for you.

     put  %sysfunc(quote([open(&excelvariable)]));

     put  %sysfunc(quote([run(&macrovariable)]));