BookmarkSubscribeRSS Feed
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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. 

4 REPLIES 4
paulkaefer
Lapis Lazuli | Level 10

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ody
Quartz | Level 8 Ody
Quartz | Level 8

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.

rogerjdeangelis
Barite | Level 11
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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4112 views
  • 1 like
  • 4 in conversation