PaigeMiller wrote:
There are many Excel files, and I want a program to run unattended, overnight, on a regular schedule. I am not aware of a programmatic way to change Excel files to CSV.
/*input*/ filename workbook "C:\Documents and Settings\&sysuserid\My Documents\Book1.xls";
/*output*/ filename txtout "C:\Documents and Settings\&sysuserid\My Documents";
data Sheets(keep=indx csvpath);
/* gather info */
length workbook txtout script filevar command $256;
workbook = pathname('WORKBOOK');
txtout = pathname('TXTOUT');
script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs');
filevar = script;
/* write the script */
file dummy1 filevar=filevar;
put 'Const ' workbook=$quote256.;
put 'Const ' txtout=$quote256.;
put 'Const xlCSV = 6';
put 'Set objExcel = CreateObject("Excel.Application")';
*put 'Set objExcel = GetObject(, "Excel.Application")';
put 'With objExcel';
put +3 '.Visible = False';
put +3 '.DisplayAlerts = False';
put +3 'Set objWorkbook = .Workbooks.Open(workbook)';
put +3 'i = 0';
put +3 'Set colSheets = .WorkSheets';
put +3 'For Each objSheet In colSheets';
put +6 'i = i + 1';
put +6 'WScript.echo i & " " & txtout & "\" & objsheet.name & ".csv"';
put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & ".csv", xlCSV';
put +6 'Next';
put +3 '.Application.Quit';
put +3 'End With';
/* close the script file by opening another, not used */
filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
file dummy1 filevar=filevar;
/* look at the script, not necessary but may be useful */
infile dummy2 filevar=script end=eof;
do while(not eof);
input;
putlog _infile_;
end;
/* call the script */
command = catx(' ','cscript',quote(strip(script)),'//nologo');
infile dummy3 pipe filevar=command end=eof truncover;
do while(not eof);
input indx csvPath $256.;
output;
putlog _infile_;
end;
stop;
run;
filename workbook clear;
filename txtout clear;
proc print;
run;
