InsertDataFromLibrary works for some dataset but not for others

[thomas111]

I am trying to get SAS data into my Excel spreadsheet and my below code can get the data from SASHelp library but not from WORK library. 

Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
sas.InsertDataFromLibrary "SASApp", "SASHelp", "CLASS", Sheet1.Range("A1")

But after copying the CLASS data to WORK library in SAS EG 

proc copy in=sashelp out=home;
select class;
run;

And changing the VBA code in my Excel to 

sas.InsertDataFromLibrary "SASApp", "WORK", "CLASS", Sheet1.Range("A1")

It gives me the below error:

Has anyone encounter the same issue and how did you resolve this? 

Thanks for your help 🙂

[SASKiwi]

Try copying to WORK rather than HOME.

[thomas111]

Sorry, when I actually tried I had WORK library in PROC COPY statement. I can also see the CLASS dataset added into the WORK library.

[thomas111]

I can use the _PRODSAVAIL dataset in the WORK library and it works fine but not any other dataset in the WORK library.

[fifthand57th · Accepted solution]

@thomas111 

The WORK library in EG and the WORK library in the SAS Add-in are not the same libraries. Enterprise Guide instantiates it's own workspace server session with a unique WORK library for that session of EG. The SAS Add-in does the same. They do not share a WORK library.

In Excel run the following code using SAS Programs to create the CLASS data set in the WORK library in the current workspace server session:

data class;
set sashelp.class;
run;

Then run the following VBA in the same session of Excel:

Sub InsertDataView()

  Dim sas As SASExcelAddIn
  Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object

  Dim data As SASDataView
  Set data = sas.InsertDataFromLibrary("SASApp", "WORK", "CLASS", Sheet1.Range("A5"), 10, False, "Sex='F'", "Age DESC", True)

End Sub

Should see the results below:

[thomas111]

Thanks. That makes sense why it didn't work. 

Appreciate your help