DATA Step, Macro, Functions and more

How can SAS macro variables be resolved in a VB script?

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

How can SAS macro variables be resolved in a VB script?

Hi all,

I have the following SAS code which opens a specific file (PEOPLE_TEST.xml) and converts it to XLS.

However, the macro variables &path and &filename would not resolve when I open the vb script (PLIST.vbs).


I would like to know how can the SAS macro variables resolve when the vb script is made. Thank you!

/* Here is the SAS code: */


%let path = C:\Users\angelique.castaneda\Documents\Work\98_SAS References;

%let filename = PEOPLE_TEST;


%macro VB;

DATA _NULL_;

FILE "&path.\PLIST.vbs";

PUT 'Set objExcel = CreateObject("Excel.Application")';

PUT 'objExcel.Visible = FALSE';

PUT 'objExcel.DisplayAlerts = FALSE';

PUT 'Set objWorkbook = objExcel.Workbooks.Open(""&path".\&filename..xml")';

PUT 'objExcel.ActiveWorkbook.SaveAs"&path.\&filename..xls",51';

PUT 'objExcel.ActiveWorkbook.Close';

PUT 'objExcel.Quit';

RUN;

%mend;

%VB;


Accepted Solutions
Solution
‎11-17-2014 12:10 AM
PROC Star
Posts: 7,492

Re: How can SAS macro variables be resolved in a VB script?

Posted in reply to angeliquec

The two lines with macro variables are enclosed in single quotes, while macro variable won't resolve unless unquoted or ONLY surrounded by double quotes.

There is a way to do that directly using multiple sets of double quotes, or using something like:

PUT 'Set objWorkbook = objExcel.Workbooks.Open("'@;

PUT "&path.\&filename..xml"@;

PUT '")';

View solution in original post


All Replies
Solution
‎11-17-2014 12:10 AM
PROC Star
Posts: 7,492

Re: How can SAS macro variables be resolved in a VB script?

Posted in reply to angeliquec

The two lines with macro variables are enclosed in single quotes, while macro variable won't resolve unless unquoted or ONLY surrounded by double quotes.

There is a way to do that directly using multiple sets of double quotes, or using something like:

PUT 'Set objWorkbook = objExcel.Workbooks.Open("'@;

PUT "&path.\&filename..xml"@;

PUT '")';

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 251 views
  • 0 likes
  • 2 in conversation