topic Re: Exporting to Excel - Excel file has all tabs (sheets) selected. How to prevent? in Microsoft Integration with SAS

Exporting to Excel - Excel file has all tabs (sheets) selected. How to prevent?

ucdcrush — Fri, 21 Dec 2012 17:47:47 GMT

I am using the following code to export 3 SAS datasets to separate sheets in an Excel file. The exporting works fine, the file is small and has no bells or whistles. The problem is, when I open the Excel file, all 3 tabs are selected. When all tabs are selected, any change made on any cell will alter that cell on all selected tabs, which could be trouble if a user does not notice that all tabs are selected. One can manually open the Excel file, click on any tab but the first one, and then only that (single) tab/sheet will be selected.

Is there a way to tell SAS (or Excel?) not to select all sheets by default after SAS creates them? I have many of these sheets and would like to avoid opening each one, clicking on one of the tabs, then saving it. (I'm sure it can be done on a folder of files in VBA, but I am hoping SAS has some way to change this grouping behavior).

Thanks.

---------------------------

LIBNAME finbook EXCEL 'C:\test.xlsx' VER=2007;

proc sql;
create table finbook.data as
select * from ds.Final_3_named;
create table finbook.variables as
select * from ds.datainfo;
quit;

DATA finbook.class;
SET sashelp.class;

run;

libname finbook clear;
---------------------------

Re: Exporting to Excel - Excel file has all tabs (sheets) selected. How to prevent?

Reeza — Fri, 21 Dec 2012 17:55:48 GMT

I don't have that issue and haven't seen it before.

Do you have a run before the final libname statement though? I get errors if I don't.

I also don't specify ver=2007 in the original libname.

Re: Exporting to Excel - Excel file has all tabs (sheets) selected. How to prevent?

ucdcrush — Fri, 21 Dec 2012 18:02:06 GMT

Hi Reeza - I corrected the first post, I think I left that 'run;' part out of what I pasted in.

Anyways I tried by specifying .xls file and commented out the 2007, and it worked in that the tabs were not selected (grouped) when I opened the file. I guess that is a simple work around, even though puzzling why it happens with version = 2007. Thank you for replying.

Re: Exporting to Excel - Excel file has all tabs (sheets) selected. How to prevent?

Reeza — Fri, 21 Dec 2012 18:10:56 GMT

You can look at using excelcs and pc files server instead of ver=2007 to get an xlsx file if you want.