topic Re: Hide Excel Sheet after Export in SAS Programming

Hide Excel Sheet after Export

Ody — Mon, 15 Aug 2016 19:45:30 GMT

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.

Re: Hide Excel Sheet after Export

paulkaefer — Mon, 15 Aug 2016 20:02:10 GMT

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;

Re: Hide Excel Sheet after Export

RW9 — Tue, 16 Aug 2016 08:50:35 GMT

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.

Re: Hide Excel Sheet after Export

Ody — Tue, 16 Aug 2016 23:57:08 GMT

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.

Re: Hide Excel Sheet after Export

rogerjdeangelis — Thu, 18 Aug 2016 21:28:27 GMT

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