Is there a non DDE option to hide an excel worksheet after data is exported to an excel file?
I am exporting data via proc append and despite my best efforts everytime I open the workbook after successfully exporting the data, the sheet I want to remain hidden is always visible. I've tried setting it to very hidden in the VBA editor but that doesnt work; when ever I export data to the file it unhides the sheet.
Any tips/suggestions are appreciated. Thanks.
This Stack Overflow thread suggests using the following:
filename sas2xls dde 'excel|system';
data _null_;
file sas2xls;
put '[open("C:\Users\Dmitry\Google Drive\SAS\test.xlsx")]';
put '[workbook.hide("Sheet1")]';
put '[error(false)]';
put '[save()]';
put '[file.close(false)]';
run;
Hi,
The simple answer is no. SAS supports the import and export of data to and from Excel, it does not support specific Excel functions. You could use DDE, however I would advise against using a very old unsupported technology. Why do you want to export data to Excel but not show it, this doesn't make any sense? Is it perhaps codelists or something similar - then look at the problem from the other end, build an Excel file which can read in CSV data, then your SAS program can just dump CSV data up for Excel to read on open. Or the best solution in all these cases - stop using Excel. Use a database, or reporting suite.
From the perspective of using SAS only, these replies are what I guessed my options would be after researching this for a few days; DDE or nothing.
Thanks for the input.
SAS Forum: Hiding Rows in Excel or an entire sheet
XLConnect can hide a sheet in an existing work book
but it cannot hide the only sheet in a workbook.
HAVE ( Two sheets(males and females) in workbook
Up to 40 obs from xls.males total obs=10
ROW NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Henry M 14 63.5 102.5
3 James M 12 57.3 83.0
4 Jeffrey M 13 62.5 84.0
5 John M 12 59.0 99.5
6 Philip M 16 72.0 150.0
7 Robert M 12 64.8 128.0
8 Ronald M 15 67.0 133.0
9 Thomas M 11 57.5 85.0
10 William M 15 66.5 112.0
males|females
Up to 40 obs from xls.females total obs=9
ROW NAME SEX AGE HEIGHT WEIGHT
1 Alice F 13 56.5 84.0
2 Barbara F 13 65.3 98.0
3 Carol F 14 62.8 102.5
4 Jane F 12 59.8 84.5
5 Janet F 15 62.5 112.5
6 Joyce F 11 51.3 50.5
7 Judy F 14 64.3 90.0
8 Louise F 12 56.3 77.0
9 Mary F 15 66.5 112.0
males/females
WANT to show only one sheet (hide the entire female sheet)
Up to 40 obs from xls.males total obs=10
ROW NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Henry M 14 63.5 102.5
3 James M 12 57.3 83.0
4 Jeffrey M 13 62.5 84.0
5 John M 12 59.0 99.5
6 Philip M 16 72.0 150.0
7 Robert M 12 64.8 128.0
8 Ronald M 15 67.0 133.0
9 Thomas M 11 57.5 85.0
10 William M 15 66.5 112.0
males
SOLUTION
Here is the working part of the solution
%utl_submit_r64('
library(XLConnect);
wb <- loadWorkbook("d:/xls/hid.xlsx");
hideSheet(wb,sheet="females");
saveWorkbook(wb);
');
Again it takes more code to create the sample worksheets
%utlfkil(d:/xls/hid.xlsx);
libname xls "d:/xls/hid.xlsx";
data xls.males xls.females;
set sashelp.class;
if sex='M' then output xls.males;
else output xls.females;
run;quit;
libname xls clear;
%utl_submit_r64('
library(XLConnect);
wb <- loadWorkbook("d:/xls/hid.xlsx");
hideSheet(wb,sheet="females");
saveWorkbook(wb);
');
Only this is visible
Up to 40 obs from xls.males total obs=10
ROW NAME SEX AGE HEIGHT WEIGHT
1 Alfred M 14 69.0 112.5
2 Henry M 14 63.5 102.5
3 James M 12 57.3 83.0
4 Jeffrey M 13 62.5 84.0
5 John M 12 59.0 99.5
6 Philip M 16 72.0 150.0
7 Robert M 12 64.8 128.0
8 Ronald M 15 67.0 133.0
9 Thomas M 11 57.5 85.0
10 William M 15 66.5 112.0
males
As a side note you can hide rows with the amazing 'ODS EXCEL'
product.
* this hides all rows;
ods excel file="d:/xls/hidden.xlsx";
ods excel options(HIDDEN_ROWS='1-20');
ods excel options(sheet_name="sheet1" sheet_interval="none");
proc report data=sashelp.class;
run;quit;
ods excel close;
ODS excel is very powerfull
see
http://goo.gl/1A5rrp
http://support.sas.com/documentation/cdl/en/odsug/67921/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04zeur27rv.htm
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.