Hi SAS Users,   I have come across an issue where, if I read (proc import) a sheet from excel file it is reading old values from columns which contains formula - even though the values have been updated because of the source cells being updated with new values.   For example: In a sheet named 'TestSheet' in file 'testsheet.xlsx' I have following values.   DataHere FormulaHere 10 11 9 10   Where first column is in a range called 'DataRange' and the second column is in a range called 'FormulaRange'. First column has numerical values (in this case 10 and 9) and second column has formula that adds 1 to value in corresponding A column i.e. formula in B2 is A2+1 and in B3 is B2+1, resulting in values 11 and 10 respectively.   If I run proc import now: proc import datafile= "\\<filepath>\testsheet.xlsx" out= TestDataSet dbms=xlsx replace; sheet="TestSheet"; GETNAMES = YES; run; I get values as expected. DataHere  FormulaHere 10             11   9             10   Now, if I run following script to update the values in column A to 1 and 2 using SAS dataset 'testdata': data testdata; input DataHere; datalines; 1 2 ; libname xl Excel "\\<filepath>\testsheet.xlsx"; proc sql; drop table xl.DataRange; create table xl.DataRange as select * from testdata; quit; libname xl clear; Then it success fully updates the values in column A; which should have updated the values in column B as well.   However, when I run proc import again to read data from the sheet: proc import datafile= "\\<filepath>\testsheet.xlsx" out= TestDataSet dbms=xlsx replace; sheet="TestSheet"; GETNAMES = YES; run;   SAS only gets updated values from column A but not column B (gets old values): DataHere  FormulaHere 1              11 2              10   Even though when I open the excel spreadsheet I can see the values updated in column B as well:  DataHere  FormulaHere 1              2 2              3     Is there a way to get SAS to read the updated Data?   ... View more

Hi SAS Users,   I was wondering if anyone has come across similar issue as I am; and if so, what the solution was.   I use SAS Enterprise Guide, and one of the task I am working on requires me to edit Excel 'Sheet Name'. I could not find any solution that can be scripted within SAS, so I created a VBScript that does the sheet rename task. When I run this VBScript via command prompt it works but when I try to run it via Enterprise Guide, it does not work (need this to run via SAS for automation). It does not throw any error but does not run either (i.e. does not rename the sheet). I have tried following three methods that I found online, but same result with all of them.   Method 1 (running command line code using sysexec): %sysexec cscript "\\filepath\filename.vbs";   Method 2 (creating bat file to run command line code, and using x command to run the batch file): FILENAME script "\\filepath\filename.bat";   DATA __NULL__; FILE script; PUT 'cscript "\\filepath\filename.vbs"'; RUN;   X script;   Method 3 (running the pipe command to run batch file): filename oscmd pipe '"\\filepath\filename.bat" 2>&1';   data _null_; infile oscmd; input; put _infile_; run;     I noticed that the Enterprise Guide version in my laptop is 64bit but the SAS Add-In for Microsoft Office is 32bit. Could that be the reason? [VBScript uses WScript.CreateObject("Excel.Application") fuctionality] Or am I missing something? ... View more