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

[angeliquec]

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;

[art297 · Accepted solution]

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