Excel外接程序用于Microsoft (AMO) -使用VBA删除存储的进程对象并保留值

Question

软件版本

SAS：9.4M4

MicrosoftOffice8.0的SAS外接程序

Excel：2016年

下午好,

我有两个存储进程：

第一个输入输入到Excel电子表格中的各种单元格中，查询数据集并将值返回给64个非连续单元格。

第二个获取来自64个非连续单元的值(这些单元格中的一些值将由最终用户更改，因此需要第二个存储进程)，并调用一系列代码元素来转换数据，以便将其加载到另一个表中。

听起来很简单，但是我无法以提示的形式将64单元格的值传递给第二个存储进程，因为数据存储在第一个存储的process对象中，因此不能用作提示值。为了解决这个问题，我决定通过在自动出现的SAS面板中从home选项卡中选择“关闭选择项”来记录一个宏(如所附的屏幕快照所示)。

​

​

这正好完成了我希望它做的事情，并断开了与存储的process对象的连接，同时保留了值( delete方法无法完成的事情)。此过程生成的VBA是：

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

问题是，每次创建新的存储过程对象时，都会分配新的对象号(这是完全合理的)。问题是，这是否存储在可以获取它的某个地方，并将其构建到我的VBA中，因此，无论对象的标识符是什么，我都能够断开存储的进程对象的连接？

任何帮助或洞察力都将不胜感激。

史考特

Answer 1

史考特,

我是否可以重新定义一个问题，那就是你所要做的事情的目的是什么。我提到这一点，是因为你正在使用一个有缺陷的过程开始，然后你正在处理这个有缺陷的开始的后果。

Excel文件xlsx是一个被压缩的XML文件。在许多现代应用程序中，系统甚至不需要Excel来生成和读取Excel。与其使用VBA，不如考虑切换到一个读取和写入Excel与SAS Excel外接程序的系统。然后在你完成你的目标后使用SAS。

请考虑以下几点:使用.NET Core和EPPlus。下载，为EPPlus添加Nuget包，读取和写入Excel文件，然后忘记VBA。使用C#作为语言基础。然后在需要的地方应用SAS外接程序。我认为这将花费一些时间学习，并为您节省了大量的头痛长期。VBA工作，但相当不受欢迎。

这是一位专业从事SAS/Excel业务的前SAS顾问。这个过程比你正在做的要快多少?可能是1000倍。

主持人可能会因为缺乏直接答案而感到不安，但你在问的是如何拯救你在远洋航行中所经历的一次黑暗的航行。从一艘更好的船开始，忽略救助问题。