Remove sas content from Excel with VBA (MS Office Add-in)

Question

I've figured out how to use the MS Office Add-in to run a SAS Stored Process from within Excel (using an input stream) to obtain outputs.

Everytime I run this, new columns are inserted to accommodate the new output. Also, additional content is added to the workbook.

My understanding of the SAS Add-in object model is poor, so I was hoping someone could help me:

1) Remove the existing content with VBA before running the process again; or 2) Write VBA code to refresh existing content only (within the same output range).

The code I'm using is:

Sub sasTests()

Application.ScreenUpdating = False

Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("SAS.ExcelAddIn").Object
Dim inputStream As SASRanges
Set inputStream = New SASRanges
inputStream.Add "Prompts", Worksheets("sasInput").Range("sasInput")
sas.InsertStoredProcess "/Shared Data/C139/sasTests", _
    Worksheets("sasOutput").Range("A1"), , , inputStream

End Sub

Many thanks!

PS. If anyone has any handy references for the SAS Add-in for MS Office, please provide links

Answer 1

You could clear the range containing the existing content with the .Clear method. If this range were in columns M:P, you could use the following line:

sheetNameHere.Range("M:P").Clear

If you added that line after setting

Application.ScreenUpdating = False

I believe this would imitate a "refresh" of the data.

Answer 2

This will remove all old components from the workbook. I am not sure how to refresh all but assuming its similar:

Sub DeleteSasContent()Dim sas As SASExcelAddIn
Set sas = Application.COMAddIns.Item("sas.exceladdin").Object

Dim stpList As SASStoredProcesses
Dim dataList As SASDataViews
Dim pivotList As SASPivotTables
Dim reportList As SASReports

Set stpList = sas.GetStoredProcesses(ThisWorkbook)
Set dataList = sas.GetDataViews(ThisWorkbook)
Set pivotList = sas.GetPivotTables(ThisWorkbook)
Set reportList = sas.GetReports(ThisWorkbook)

For i = 1 To stpList.Count
stpList.Item(i).Delete
Next i

For i = 1 To dataList.Count
dataList.Item(i).Delete
Next i

For i = 1 To pivotList.Count
pivotList.Item(i).Delete
Next i

For i = 1 To reportList.Count
reportList.Item(i).Delete
Next i

End Sub

See the SAS help here http://support.sas.com/kb/45/606.html