@lambdavu
Just want to be certain that I understand what you want to do first... SAS Add-in for MS Office connects to local SAS and you want to create a SAS data set from data in an Excel worksheet. Is this accurate?
Every client (EG or SAS Add-in, etc.) instantiates a new SAS workspace server session on whatever "server" the client connects to (local or remote). So EG connecting to local SAS starts a sas.exe process for it alone. The SAS Add-in also starts a sas.exe process for it alone. If you assign libraries using "Assign Project Library" task or in SAS code in EG, those libraries will not be visible/available in other clients.
If you have the SAS Add-in for MS Office version 7.13 or later, you can run SAS code directly from the SAS ribbon in Excel. Thus, you can assign your libraries by running LIBNAME statements directly from Excel. Side Note: If you do not have the latest version of the SAS Add-in (7.15), we highly recommend upgrading to one of the two latest versions as they have "A" level support. See SAS Note 59821.
And yes, you can use the "Copy to SAS Server" task to create a SAS data set from Excel worksheet data. You will need to issue your LIBNAME statements prior to using this task using "Programs" on the SAS ribbon in Excel if you have version 7.13 or later.
If you do not have SAS Add-in version 7.13 or later, you can create an autoexec.sas file that contains your LIBNAME statements and save it in the \SASFoundation\9.4\ installation directory on your machine. The default is C:\Program Files\SASHome\SASFoundation\9.4\. When the SAS Add-in (or other clients, like EG, that connect to the same local server) instantiates it's local workspace server, the autoexec.sas file will execute and the libraries defined in it will be available in the SAS Add-in when connected to Local server. (So make sure you restart Excel and/or EG, when you create an autoexec.sas.)
Hope this helps.
