EG Custom Task - Reading in data from Excel

[ChrisBrooks]

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?

[ChrisBrooks]

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

[ChrisHemedinger · Accepted solution]

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

[ChrisBrooks]

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