The SAS Output Delivery System and reporting techniques

Selecting Multiple Tabs in Excel when opening for the first time

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Selecting Multiple Tabs in Excel when opening for the first time

Hey guys,


Here is a very particular problem, and one that I have been unable to find any solution to. It is more of a user preference but would make my life a lot easier.

 

I have created a program that creates daily reports that are then exported to Excel, however the data is divided into multiple tabs, as per working group.

 

The report is run at the start of each day, and during the day, each group adds to the excel worksheet in relation to their work.

When the program is run the following day, it first imports the previous days work, and then adds new records to that have come in, then exports it out again for the data users. All this works well.

 

Some of the users do not have IT backgrounds, so I am trying to make this thing as streamlined as possible to avoid potential problems that have arisen in the past. The work sheets are both different, however, on startup (the first time opening the excel file for the day) all of the tabs are selected in excel. Some users have just begun entering data once the report is opened, not realizing that all tabs are selected. As such, whatever they write in one cell appears in all cells on every other tab, which overwrites whatever data is in that cell. This leads to significant issues.

 

Is there any way to ensure that when you open excel after being exported from SAS that only one tab is selected, instead of all of them?

 

Really appreciate any input someone might have.

Noob.


Accepted Solutions
Solution
‎05-01-2017 09:23 AM
PROC Star
Posts: 7,428

Re: Selecting Multiple Tabs in Excel when opening for the first time

[ Edited ]

Like I said, this is new to me. However, the behavior you describe definitely can exist and can be corrected.

 

My guess is that your original workbook (that you're updating) has grouped worksheets. You can see if that is the case simply by opening the workbook.

 

I've attached a screenshot of such a workbook. If you look at the upper left corner (where the workbook name is shown) you'll see that the name is followed by the string "[Group]"

 

Simply right-click on one of the sheets and select "Ungroup", then save the file.

 

Art, CEO, AnalystFinder.com

 


Capture.JPG

View solution in original post


All Replies
PROC Star
Posts: 295

Re: Selecting Multiple Tabs in Excel when opening for the first time

What are you doing to export the data?

Occasional Contributor
Posts: 10

Re: Selecting Multiple Tabs in Excel when opening for the first time

Just a simple proc export, with a replace option so that the previous days table is overwritten. Nothing overly fancy.

 

proc export data= ...

outfile ...

dmbs= excel replace

sheet=...

run

 

 hope this helps

PROC Star
Posts: 295

Re: Selecting Multiple Tabs in Excel when opening for the first time

And only one tab is selected before the export, but then after export all tabs are selected when you open the file? I'll see if I can replicate the behavior, but I've never seen it before.

PROC Star
Posts: 295

Re: Selecting Multiple Tabs in Excel when opening for the first time

Actually, I'm not even sure how you're ending up with multiple tabs if you're overwriting the existing workbook. When I run proc export, I only end up with one tab. So maybe I do not understand what your actual issue is?

Occasional Contributor
Posts: 10

Re: Selecting Multiple Tabs in Excel when opening for the first time

In my case, I have two tables, which are ultimately translated into two excel sheets. I have 2 seperate export statements, both referencing the same outfile destination (the excel file), but both have different sheet names specified in the Sheet=..

 

Hope this helps a little. Would post the code but all is stored on a seperate network with no internet access.

 

 

PROC Star
Posts: 7,428

Re: Selecting Multiple Tabs in Excel when opening for the first time

I, too, want to know how you are creating the workbooks .. so that I never do the same thing. In all of the years I've been working with Excel I never realized that one could have multiple worksheets open. Nasty!

 

As for a solution, you could always run a really short vb script that made all worksheets (but one) active and, in the same script, you could select which one to make active. Then, have the vb script resave the workbook or, if you're going to have multiple versions (with different sheets made active, have it save each copy.

 

Art, CEO, AnalystFinder.com

 

Solution
‎05-01-2017 09:23 AM
PROC Star
Posts: 7,428

Re: Selecting Multiple Tabs in Excel when opening for the first time

[ Edited ]

Like I said, this is new to me. However, the behavior you describe definitely can exist and can be corrected.

 

My guess is that your original workbook (that you're updating) has grouped worksheets. You can see if that is the case simply by opening the workbook.

 

I've attached a screenshot of such a workbook. If you look at the upper left corner (where the workbook name is shown) you'll see that the name is followed by the string "[Group]"

 

Simply right-click on one of the sheets and select "Ungroup", then save the file.

 

Art, CEO, AnalystFinder.com

 


Capture.JPG
Occasional Contributor
Posts: 10

Re: Selecting Multiple Tabs in Excel when opening for the first time

Hey Art,

 

You are definitely on to something. The tabs/sheets are grouped (as per the description at the top of the page when opening). I work in excel all the time, and didn't know this was a thing...  

 

Going back to your previous comment, inserting VBScript into SAS is beyond me. I will do some of my own digging to figure out how to add something to my program to "ungroup" during/after the export, but if this is something you're skilled at, any additional help would be greatly appreciated.

 

Thanks for you help

PROC Star
Posts: 7,428

Re: Selecting Multiple Tabs in Excel when opening for the first time

[ Edited ]

No VB script is needed. Sounds like the worksheets are grouped in the workbook you are updating. If so, simply open it in Excel (before updating it), remove the grouping, save the file, and then do your update.

 

Grouping occurs when one has a worksheet open and then clicks on another worksheet while the shift key is depressed.

 

That should only be a one-time fix. However, if the grouping is occuring when you initially create the workbook, I would definitely be interested in seeing the code and data that are creating the workbook as that simply shouldn't happen.

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 10

Re: Selecting Multiple Tabs in Excel when opening for the first time

[ Edited ]

Thanks for the reply Art.

 

I don't want to elaborate on the code in too much detail (to save you the headache more than anything else), but suffice to say this will be an issue going forward, and simply opening and saving will result in a once cycle fix.

 

The program is run periodically. The first time, lets say 2017w1, the excel files will not exist, so the program will create file them based on the underlying sas data. If I open them on 2017w1d1, and ungroup then save them, they will be good for the week. However, when they are run for 2017w2, I would have to go in and do this ungrouping activity again, and again and again.

 

I thank you for helping me identify the problem, just need to figure out the work around now...  

 

update - would prefer not to go the whole create template, run from template VBA route if possible..

Occasional Contributor
Posts: 10

Re: Selecting Multiple Tabs in Excel when opening for the first time

Hey Art, thanks for your help figuring this out...

 

Yes, what you proposed would certainly work, but like you said, is a one time fix, which wont do. This process is run regularly, so would like to automate this if possible.

 

As alluded to above in another comment, the code is very simple:


Proc export data= ....

   (where=(group=..)

   outfile= ...

   dmbs= excel replace;

   sheet= "1"

  run;

 

This same code is repeated with sheet=2, sheet=3.. etc.

 

Somehow this process is creating the grouping.

 

If going the VBA route, would really like to avoid the whole template approach if possible...

 

Any help is greatly appreciated!

 

 

PROC Star
Posts: 7,428

Re: Selecting Multiple Tabs in Excel when opening for the first time

I still don't understand how the worksheets are getting grouped, but the following SAS code opens a file that has at least two worksheets labeled 1 and 2 and, if the worksheets are grouped, will ungroup them. Thus, if you run the following code after you run your proc exports, the result should be an ungrouped workbook (note: you have to change c:\art\test.xls to the path of the actual file, and if your worksheets aren't labeled 1 and 2, you'll have to change those names as well):

 

data _null_;
  length script filevar $256;
  script = catx('\',pathname('WORK'),'PasteIt.vbs');
  filevar = script;
  script="'"||'cscript "'||trim(script)||'"'||"'";
  call symput('script',script);
  file dummy1 filevar=filevar recfm=v lrecl=512;
    
  put 'Dim objExcel';
  put 'Dim OldBook';

  put 'set objExcel = CreateObject("Excel.Application")';
  put 'Set OldBook=objExcel.Workbooks.Open("c:\art\test.xlsx")';
  put 'OldBook.Sheets("1").Select';
  put 'OldBook.Sheets("2").Select';
  put 'objExcel.DisplayAlerts = False';
  put 'OldBook.Save';
  put 'OldBook.Close';
  put 'objExcel.DisplayAlerts = True';
  put 'objExcel.Quit';
run;

data _null_;
  call system(&script.);
run;

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 10

Re: Selecting Multiple Tabs in Excel when opening for the first time

[ Edited ]

You've gone above and beyond to help me get through this. I really appreciate it.

 

While I will happily press on if you're bored of dealing, but still have one additional issue.

 

The code you sent is great as long as the filepath is explicity specified in the

  put 'Set OldBook=objExcel.Workbooks.Open("c:\art\test.xlsx")';   line.

 

I have a defined a macro variable using the %let at the beginning of my program which takes into account the changing dates which I would like to use in place of this hardcoding. It is essentially the save loaction on our network with a date variable that changes each period. I guess it isnt as simple as replacing this c: drive location with my macro variable (excel doesnt like sas macros?).   

 

Otherwise, this script does everything it was intended to do!

 

Thanks again!

 

Attached a screen grab I was able to capture in the brief moment it was processing if it helps figure out what is going on here..


Untitled.png
PROC Star
Posts: 7,428

Re: Selecting Multiple Tabs in Excel when opening for the first time

SAS works well with macro variables, you just have to be careful how you use them. I think that the following does what you described:

 

%let thefile=c:\art\test.xlsx;

data _null_;
  length script filevar $256;
  script = catx('\',pathname('WORK'),'PasteIt.vbs');
  filevar = script;
  script="'"||'cscript "'||trim(script)||'"'||"'";
  call symput('script',script);
  file dummy1 filevar=filevar recfm=v lrecl=512;
    
  put 'Dim objExcel';
  put 'Dim OldBook';

  put 'set objExcel = CreateObject("Excel.Application")';
  script=catt('Set OldBook=objExcel.Workbooks.Open("',"&thefile.",'")');
  put script;
  put 'OldBook.Sheets("1").Select';
  put 'OldBook.Sheets("2").Select';
  put 'objExcel.DisplayAlerts = False';
  put 'OldBook.Save';
  put 'OldBook.Close';
  put 'objExcel.DisplayAlerts = True';
  put 'objExcel.Quit';
run;

data _null_;
  call system(&script.);
run;

Art, CEO, AnalystFinder.com

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 520 views
  • 0 likes
  • 3 in conversation