SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

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

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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

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;
---------------------------


Accepted Solutions
Solution
‎12-21-2012 12:55 PM
Grand Advisor
Posts: 16,315

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

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.

View solution in original post


All Replies
Solution
‎12-21-2012 12:55 PM
Grand Advisor
Posts: 16,315

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

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.

Contributor
Posts: 38

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

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.


Grand Advisor
Posts: 16,315

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

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

Post a Question
Discussion Stats
  • 3 replies
  • 509 views
  • 0 likes
  • 2 in conversation