DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 53
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,363

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

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,363

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

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.

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

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