Desktop productivity for business analysts and programmers

EG Custom Task - Reading in data from Excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 132
Accepted Solution

EG Custom Task - Reading in data from Excel

I am trying to write an EG Custom Task to read into a SAS data set a range from an Excel worksheet which the user first selects and then assigns the columns to roles using the SAS Variable Selector.

I'm using ISASTaskDataAccessor to get information about the columns and everything seems to be working fine until I try to use the Consumer.ActiveData.Library and Consumer.ActiveData.Member in my SAS program - the log contains the following error: "ERROR: File WORK._EXCEL_.DATA does not exist".

I've looked at the code in Chris Hemedinger's book for the TopNReport example and also the code on GitHub for this and can't see any extra steps which might be needed to make this range available in WORK.

Does anyone have any ideas?


Accepted Solutions
Solution
‎06-01-2015 07:54 PM
Community Manager
Posts: 2,703

Re: EG Custom Task - Reading in data from Excel

Chris,

The Office Add-In will copy the data to the SAS session (in the manner you observed) just before your task is run, but not at the time that your UI is being shown.  If you need that to happen while your task is visible, then we'll need to look at the cues the Office Add-In needs in order to make that happen.

You could take a look at the Data Set to DATA Step task, which emulates some of what the Office Add-in is doing to copy the data.  The source for that is here.

Chris

View solution in original post


All Replies
Frequent Contributor
Posts: 132

Re: EG Custom Task - Reading in data from Excel

I think I'm slightly closer to the root of the problem now. In the Office add-in options I set the option to show the log after the task has run as well as showing a log of my own via a message box. It appears that what is happening is that my code is running (I'm using the SubmitSasProgramandWait method) and then afterwards the data is being imported into the work library via a data step using infile cards - and is then immediately deleted!

Placing a similar data step in my code doesn't work so somehow I need to get the Excel data into my work library before my code runs but as I said previously I can't find any reference as to how this done........

Solution
‎06-01-2015 07:54 PM
Community Manager
Posts: 2,703

Re: EG Custom Task - Reading in data from Excel

Chris,

The Office Add-In will copy the data to the SAS session (in the manner you observed) just before your task is run, but not at the time that your UI is being shown.  If you need that to happen while your task is visible, then we'll need to look at the cues the Office Add-In needs in order to make that happen.

You could take a look at the Data Set to DATA Step task, which emulates some of what the Office Add-in is doing to copy the data.  The source for that is here.

Chris

Frequent Contributor
Posts: 132

Re: EG Custom Task - Reading in data from Excel

Chris

Many thanks - now you say that it all makes perfect sense! I don't actually need the UI to be shown during execution (in fact it's probably better if it isn't) so I've rejigged my code so it now definitely runs after the data is copied to work and everything works perfectly now.

Your suggestion about looking at the Data Set to DATA Step task is also helpful anyway as I'll probably be writing another Custom Task soon where I will want the UI to remain open.

Chris

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 389 views
  • 0 likes
  • 2 in conversation