Excel AMO - Deleting Stored Process Object and Retaining Values using VBA

[Scott_Mitchell]

Hi All and specifically @fifthand57th (who seems to know all),

I have two stored processes:

The first takes input entered into various cells within the Excel spreadsheet, queries a Dataset and returns the values to 64 non-contiguous cells.

The second takes the values from the 64 non-contiguous cells (some values in these cells will be changed by the end-user, hence why there needs to be a second stored process) and calls a series of code elements to transform the data so that it can be loaded into another table.

Sounds pretty simple, however I am unable to pass the values from the 64 cells as prompts to the second stored process because the data is stored in the first stored process object and therefore can't be used as prompt values.  To counter this issue I decided to record a macro by selecting the "Close Select Items" from the home tab in the automatically surfaced SAS Panel (as per the attached screenshot).

This does precisely what I want it to do and disconnects from stored process object whilst retaining the values (something that the delete method fails to do).  The VBA generated by this process is:

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.0").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.1").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.2").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.3").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.4").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.5").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.6").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.7").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.8").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.9").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.10").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.11").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.12").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.13").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.14").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.15").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.16").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.17").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.18").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.19").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.20").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.21").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.22").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.23").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.24").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.25").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.26").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.27").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.28").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.29").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.30").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.31").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.32").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916.33").Delete
    ActiveWorkbook.Names("_AMO_ContentDefinition_194379916").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_AFFINITY_BRAND").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_AFFINITY_ENTITY").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_AFFINITY_GROUP").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_BD_AGE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_GENDER").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_BD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_NO_DRIVERS_ALLOWED").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_RD_AGE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_1YR").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_5YR").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_GENDER").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_IS_YOUNGEST_DRIVER"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_RD_NCD" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_OCCUPATION_STATUS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_RD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_DD_YD_AGE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_GENDER").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_AGE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_LICENCE_HELD_MTHS"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_DD_YDNL_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_BREAKDOWN").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_CHOICE_OF_REPAIRER").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_PO_DRIVER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_HIRE_CAR").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_NCD_PROTECTION").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_RATE_1_FOR_LIFE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_PO_WINDSCREEN").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_PRODUCT"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_RAD_METHOD_OF_PARKING").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_RAD_POSTCODE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_ACCESS_VALUE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_AGE"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_AGREED_MARKET_VALUE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_ALARM_FITTED").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_BODY_ENGINE_MOD").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_BODY_STYLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_BULL_BAR_FITTED").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_COLOUR" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_DRIVE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_ENGINE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_FACTORY_OPTIONS").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_FINANCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_INSURED_VALUE").Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_KMS"). _
        Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_MAKE"). _
        Delete
    ActiveWorkbook.Names("_AMO_ContentLocation_194379916_OutputParameter_VD_MODEL") _
        .Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_PREVIOUS_INS").Delete
    ActiveWorkbook.Names( _
        "_AMO_ContentLocation_194379916_OutputParameter_VD_USE_CODE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_AFFINITY_BRAND").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_AFFINITY_ENTITY").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_AFFINITY_GROUP").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_BD_AGE"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_BD_GENDER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_BD_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_BD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_BD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_NO_DRIVERS_ALLOWED").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_RD_AGE"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_1YR").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_CLMS_CNT_NW_5YR").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_RD_GENDER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_IS_YOUNGEST_DRIVER").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_RD_NCD"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_OCCUPATION_STATUS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_RD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_YD_AGE"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_YD_GENDER" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YD_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YD_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YD_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_AGE") _
        .Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_LICENCE_HELD_MTHS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_LICENCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_DD_YDNL_OTHER_VEHICLE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PO_BREAKDOWN" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_CHOICE_OF_REPAIRER").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PO_DRIVER"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PO_HIRE_CAR") _
        .Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_NCD_PROTECTION").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_RATE_1_FOR_LIFE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_PO_WINDSCREEN").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_PRODUCT"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_RAD_METHOD_OF_PARKING").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_RAD_POSTCODE" _
        ).Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_ACCESS_VALUE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_AGE"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_AGREED_MARKET_VALUE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_ALARM_FITTED").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_BODY_ENGINE_MOD").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_BODY_STYLE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_BULL_BAR_FITTED").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_COLOUR"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_DRIVE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_ENGINE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_FACTORY_OPTIONS").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_FINANCE_TYPE").Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_INSURED_VALUE").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_KMS"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_MAKE"). _
        Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_MODEL"). _
        Delete
    ActiveWorkbook.Names( _
        "_AMO_SingleObject_194379916_OutputParameter_VD_PREVIOUS_INS").Delete
    ActiveWorkbook.Names("_AMO_SingleObject_194379916_OutputParameter_VD_USE_CODE") _
        .Delete
End Sub

The problem is that every time a new stored process object is created a new object number is allocated (which makes complete sense).  The question is, is this stored somewhere that I can obtain it, and build it into my VBA so regardless of the identifier for the object I will be able to disconnect the Stored Process object?

Any help or insight would be greatly appreciated.

Regards,

Scott

[fifthand57th · Accepted solution]

@Scott_Mitchell 

Hi Scott,

First, you made me laugh with the "who seems to know all". I'm just fortunate to work at SAS and I know who to contact to learn/ask questions/brainstorm. 🙂

If I understand correctly, you want to run at stored process to generate values, then delete the stored process object but retain the values the stored process rendered in the workbook. 

If that is correct, then you will love the following code. It deletes the SAS content and links using VBA, but leaves the SAS values. Please test and let me know if this works for you. 

(Also, I may be on vacation when you respond so it may be a few days before I get back to you.)

Sub DeleteSasContent()

    Dim names As names
    Set names = ThisWorkbook.names
    
    For i = names.Count To 1 Step -1
    
        Dim n As Name
        Set n = names.Item(i)
        
        If InStr(1, n.Name, "_AMO_", vbTextCompare) = 1 Then
            n.Delete
        End If
    
    Next i

End Sub

[Scott_Mitchell]

@fifthand57th - Thank you so much.  Once again proving you "Know All"..

I was actually very close to this result from my investigation over the weekend, but your solution is far more elegant.

One change I did have to make was to the string within the third argument of the InStr function.  If I left it as is I was getting a new SAS Add-In for Microsoft Office panel on the right hand side of the screen as per the screenshot below.  This continues beyond the point where the entire screen is covered in panels. 

Unsure why this is the case, but given my solution didn't do this I created an amalgam of the two using _AMO_SingleObject_ as opposed to _AMO_ and then deleted the STP using the Delete method.

Sub DeleteSTPObject()
    
    ' Iterates through a list of objects used within the ThisWorkBook
    ' If prefixed with _AMO_  then delete the stored process location.
    
    Dim names As names
    Set names = ThisWorkbook.names
    
    For i = names.Count To 1 Step -1
    
        Dim n As Name
        Set n = names.Item(i)
        
        If InStr(1, n.Name, "_AMO_SingleObject_", vbTextCompare) = 1 Then
            Debug.Print names.Item(i)
            n.Delete
        End If
    
    Next i

End Sub

I hope you are having a wonderful vacation and thank you again for your insight.

Regards,

Scott Mitchell