Hello Everyone, I have a unique problem and need some help. I need to create a SAS script that opens an excel workbook, selects everything, copies it, and then pastes it all as values, saves it, then closes it. Fairly straight forward although I have limited experience working with Excel from SAS. I wont get into the details too much, but we use this excel file to load another application, and some of my values (labels mostly) are coming out in Excel with " " quotes around them. The simplest solution is to have a short script at the end that just copy and pastes the values into the same workbook. Any help is greatly appreciated! Below is a starting point, but does not do what I want. The code below opens a workbook, selects a specific tab of a work sheet, saves then closes. I need it to copy and paste values then save and close. Its a starting point anyway, but Im out my depth here... Thanks! %macro Selecting_Excel_Sheet (Analyst); data _null_; length script filevar $256; script = catx('\',pathname('WORK'),'PasteIt.vbs'); filevar = script; script="'"||'cscript "'||trim(script)||'"'||"'"; call symput('script',script); file dummy1 filevar=filevar recfm=v lrecl=512; put 'Dim objExcel'; put 'Dim OldBook'; put 'set objExcel = CreateObject("Excel.Application")'; script=catt('Set OldBook=objExcel.Workbooks.Open("',"&FilePath.\&Analyst. &Year.Q&Quarter. FFA.xlsx",'")'); put script; put 'OldBook.Sheets("Income").Select'; put 'OldBook.Sheets("Investment").Select'; put 'objExcel.DisplayAlerts = False'; put 'OldBook.Save'; put 'OldBook.Close'; put 'objExcel.DisplayAlerts = True'; put 'objExcel.Quit'; run; data _null_; call system(&script.); run;
... View more