none
用Excel.Application Workbooks.Open打开的Excel里面自定义公式无法运行 RRS feed

  • 问题

  • 环境: Excel2007
    打开:.NET语言,excel.Workbooks.Open("文件路径");
    自定义函数:VBA和.NET的COM加载项均有尝试
    结果:自定义公式处单元格内显示#NAME?,公式栏显示插件的详细信息:='C:\Users\Lossage\AppData\Roaming\Microsoft\AddIns\ExcelTest.xla'!MyTestVBAFun(A1,B1)
    "C:\Users\Lossage\AppData\Roaming\Microsoft\AddIns\"已在信任中心添加为受信任的路径
    自定义公式只完成两个数相加。正常启动(双击图标)时公式工作正常出现相加结果。双击保存的Excel文件启动公式工作也正常。只有通过excel.Workbooks.Open("文件路径")工作不正常。没有尝试VBA中的excel.Workbooks.

    2013年1月5日 15:32

答案

  • 首先呢,不管是通过XLA,还是COM实现的UDF,只要你实现正确了,那么以ShellExecute或CreateProcess来启动Excel,就不会出现你提到的问题。

    如果通过COM(也就是你这里的Excel Interop,例如: excel.Workbooks.Open)的方式来启动Excel,那么函数类别中的“用户定义”就消失了,如果此时,你通过加载项手动添加一次,那么你的UDF就可以使用了。因此目前来说,你可以在你的启动代码中增加这一动作:

    var addin =excel.AddIns.Add(@"C:\Users\Lossage\AppData\Roaming\Microsoft\AddIns\ExcelTest.xla");
    addin.Installed = true; 

    • 已标记为答案 孟冬 2013年1月6日 9:45
    • 取消答案标记 孟冬 2013年1月6日 9:49
    • 已标记为答案 孟冬 2013年1月6日 10:02
    2013年1月6日 9:11
  • 我翻了过去的代码,实际的解决方案是通过一个辅助的Addin来完成此功能。具体做法就是:

    1,在 VS 中通过 Excel 外接程序(Addin )项目模板新建一个项目;

    2,在 ThisAddIn_Startup 中通过GUID或者名称检索目标UDF加载项(Addin)的属性 Installed 是否为 True,如果不为 True,则设置为 True;

    然后将此项目同你的XLA或者UDF COM组件一同部署到目标机器。

    • 已标记为答案 孟冬 2013年1月6日 10:02
    2013年1月6日 9:37

全部回复

  • 这个问题我确实知道是怎么回事,因为解决过,所以我们的插件现在能在这种情况下正常工作。

    具体的原因不记得了,但是肯定不是你提到的受信任路径的问题。

    你的 ExcelTest.xla存放的位置应该是:

    C:\Users\Lossage\AppData\Roaming\Microsoft\Excel\XLSTART\ExcelTest.xla

    你通过COM加载项实现的UDF的话,你可以在Windows事件查看器里看看有什么错误提示。

    你最好能把你的UDF COM加载项的注册表值截图给我看看,主要是你的 InprocServer32 的相关键值是否正确。

    • 已编辑 Skyseer 2013年1月6日 2:36
    2013年1月6日 2:21
  • 可能我对Excel开发了解太浅。我并没有通过COM加载项实现UDF,而只是在VBA的公共模块里实现的自定义公式,通过“Excel加载项”的分类加载上的。因此这里的XLA我找不到对应的InprocServer3,可否指示应处于何路径下?

    至于用.NET做的COM加载项,注册表如下

    [HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{D386C81E-E865-4F72-ACE6-CF613A33BDA1}\InprocServer32]

    @="C:\\Windows\\SysWow64\\mscoree.dll"
    "ThreadingModel"="Both"
    "Class"="MyUDF.LossageClass"
    "Assembly"="MyUDF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
    "RuntimeVersion"="v4.0.30319"
    "CodeBase"="file:///C:/Users/Lossage/documents/visual studio 2010/Projects/ExcelAutoRun/MyUDF/bin/Debug/MyUDF.dll"

    [HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{D386C81E-E865-4F72-ACE6-CF613A33BDA1}\InprocServer32\1.0.0.0]
    "Class"="MyUDF.LossageClass"
    "Assembly"="MyUDF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
    "RuntimeVersion"="v4.0.30319"
    "CodeBase"="file:///C:/Users/Lossage/documents/visual studio 2010/Projects/ExcelAutoRun/MyUDF/bin/Debug/MyUDF.dll"

    特别说明,我在双击打开Excel文件或者新建Excel文件,或者启动excel.exe的情况下,两种方式创建的公式工作都是十分正常的。

    打开使用了自定义公式的Excel时使用的代码段是

    Excel.Application app = new Excel.Application();
    app.Visible = false;
    app.Workbooks.Open("E:\\ExcelTest\\abc.xlsx");

    盼答,不胜感激。

    2013年1月6日 4:01
  • 补充一下,日志里没有发现什么关于它的纪录
    2013年1月6日 4:10
  • 可能我对Excel开发了解太浅。我并没有通过COM加载项实现UDF,而只是在VBA的公共模块里实现的自定义公式,通过“Excel加载项”的分类加载上的。因此这里的XLA我找不到对应的InprocServer3,可否指示应处于何路径下?

    至于用.NET做的COM加载项,注册表如下

    [HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{D386C81E-E865-4F72-ACE6-CF613A33BDA1}\InprocServer32]

    @="C:\\Windows\\SysWow64\\mscoree.dll"
    "ThreadingModel"="Both"
    "Class"="MyUDF.LossageClass"
    "Assembly"="MyUDF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
    "RuntimeVersion"="v4.0.30319"
    "CodeBase"="file:///C:/Users/Lossage/documents/visual studio 2010/Projects/ExcelAutoRun/MyUDF/bin/Debug/MyUDF.dll"

    [HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{D386C81E-E865-4F72-ACE6-CF613A33BDA1}\InprocServer32\1.0.0.0]
    "Class"="MyUDF.LossageClass"
    "Assembly"="MyUDF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
    "RuntimeVersion"="v4.0.30319"
    "CodeBase"="file:///C:/Users/Lossage/documents/visual studio 2010/Projects/ExcelAutoRun/MyUDF/bin/Debug/MyUDF.dll"

    特别说明,我在双击打开Excel文件或者新建Excel文件,或者启动excel.exe的情况下,两种方式创建的公式工作都是十分正常的。

    打开使用了自定义公式的Excel时使用的代码段是

    Excel.Application app = new Excel.Application();
    app.Visible = false;
    app.Workbooks.Open("E:\\ExcelTest\\abc.xlsx");

    盼答,不胜感激。

    XLA是不需要注册表项的,请你注意我给你回复的内容,把你的ExcelText.xla拷贝到这个目录下:C:\Users\Lossage\AppData\Roaming\Microsoft\Excel\XLSTART\ExcelTest.xla

    这样你打开的Excel才能正确加载此模板中编写的自定义函数。

    你注册表中的这个:

    file:///C:/Users/Lossage/documents/visual studio 2010/Projects/ExcelAutoRun/MyUDF/bin/Debug/MyUDF.dll

    不是你用COM实现的自定义公式组件吗?我想请问一下,MyUDF.LossageClass 类是从 Extensibility.IDTExtensibility2继承的吗?标记了Guid,ProgId,ClassInterface,ComVisible属性吗?

    从你这里还可以看到,你这是调试状态下,VS帮你注册的信息,所以你得在你的excel.Workbooks.Open的时候,去注册表检查,这些注册表项是否还在。另外 codeBase 要改成这样:C:/Users/Lossage/documents/visual studio 2010/Projects/ExcelAutoRun/MyUDF/bin/Debug/MyUDF.dll。

    还有注册正确后,是不需要你手动通过“Excel加载项”的分类加载的。

    2013年1月6日 5:02
  • XLA放在C:\Users\Lossage\AppData\Roaming\Microsoft\Excel\XLSTART\ExcelTest.xla里,状况没有发生改变。

    而我用COM实现的自定义公式组件,继承了Extensibility.IDTExtensibility2,但继承的接口函数我对它们没有做任何编码。是否必须执行一些特定代码?

    因为VS调试下注册的注册表在不执行清理的时候不会消失,所以我没有自行注册。为了测试我使用regasm /codebase MyTestExcelFunRun.dll注册了一次,情况和VS自动注册是一样的。即:

    鼠标双击打开xlsx文件、打开Excel.exe后拖进去xlsx文件,以上两种方式的公式工作均正常,仅在通过第三个应用程序的代码Excel.Application打开时公式不工作。

    补充说明一下,xla实现公式或com实现公式我都尝试了。而Excel.Application.WorkBooks.Open是在第三个应用程序里工作的。我的目的是自动生成Excel报表,而Excel报表调用了我的自定义公式。目前是技术测试。

    2013年1月6日 6:39
  • 你新建一个 UDF.XLS 文件,然后在里面实现一个自定义方法 Public Function UDF_SUB(NUM1 As Long,NUM2 As Long) As Long。然后将文件放置在 C:\Users\Lossage\AppData\Roaming\Microsoft\Excel\XLSTART 目录下。

    检查你的Excel,把你自定义的加载项都删除或禁用掉。然后你再用 Excel.Application.WorkBooks.Open 测试下。

    2013年1月6日 6:59
  • 如果是VBA的公式和应用公式的Excel文件在同一个xlsx文件里确实可以。但是我需要的是一个可以“污染”全局的公式,即一个xla的解决方案。

    按您的方法,公式和应用公式的Excel文件在同一个xlsx文件里,我不用在您指定的目录下也可以运行。

    但是我需要执行这个公式的xlsx文件会有很多,因此我的xla或COM需要对全局起作用。执行自定义公式的文件和公式所在的文件必须剥离。这样可以做得到吗?

    2013年1月6日 8:59
  • 首先呢,不管是通过XLA,还是COM实现的UDF,只要你实现正确了,那么以ShellExecute或CreateProcess来启动Excel,就不会出现你提到的问题。

    如果通过COM(也就是你这里的Excel Interop,例如: excel.Workbooks.Open)的方式来启动Excel,那么函数类别中的“用户定义”就消失了,如果此时,你通过加载项手动添加一次,那么你的UDF就可以使用了。因此目前来说,你可以在你的启动代码中增加这一动作:

    var addin =excel.AddIns.Add(@"C:\Users\Lossage\AppData\Roaming\Microsoft\AddIns\ExcelTest.xla");
    addin.Installed = true; 

    • 已标记为答案 孟冬 2013年1月6日 9:45
    • 取消答案标记 孟冬 2013年1月6日 9:49
    • 已标记为答案 孟冬 2013年1月6日 10:02
    2013年1月6日 9:11
  • 我翻了过去的代码,实际的解决方案是通过一个辅助的Addin来完成此功能。具体做法就是:

    1,在 VS 中通过 Excel 外接程序(Addin )项目模板新建一个项目;

    2,在 ThisAddIn_Startup 中通过GUID或者名称检索目标UDF加载项(Addin)的属性 Installed 是否为 True,如果不为 True,则设置为 True;

    然后将此项目同你的XLA或者UDF COM组件一同部署到目标机器。

    • 已标记为答案 孟冬 2013年1月6日 10:02
    2013年1月6日 9:37
  • 是的,正是您说的情况。ShellExecute或CreateProcess来启动Excel,是不会出现这个问题的。

    抱歉,我在添加这段代码之后没有变化,因此有个疑问,是应当在紧随实例化一个Excel.Application之后就立刻执行这段(我这么操作的时候被提示类 AddIns 的 Add 方法无效),而在紧随打开xlsx文件之后执行则不会报错,但是没有发生任何效果

    那如果是一个COM实现的UDF如何解决呢?


    • 已编辑 孟冬 2013年1月6日 9:53
    2013年1月6日 9:45
  • 是的,正是您说的情况。ShellExecute或CreateProcess来启动Excel,是不会出现这个问题的。

    如您所说,添加这段代码之后就可以了,谢谢!

    那如果是一个COM实现的UDF如何解决呢?

    你需要一个额外的项目来支持,我目前就是这么做的,通过一个Excel外接程序来自动安装:

    1,在 VS 中通过 Excel 外接程序(Addin )项目模板新建一个项目;

    2,在 ThisAddIn_Startup 中通过GUID找到加载项(Addin),如果它的属性 Installed 不为 True,则设置为 True;

    2013年1月6日 9:52
  • 多谢!在您的耐心指导下我已经解决了这个问题。确实如您所说。不是去在Addins去Add一个,而是需要获取出来(如果不存在的时候再Add),再置位Installed。有时候这时状态时True,但没有起作用。可以置位False后再置一次True

    非常感谢您的耐心和指导!我留下我的Email/MSN:lossage@126.com

    如有机会,希望能够进一步向您讨教!

    再次感谢。


    • 已编辑 孟冬 2013年1月6日 10:22 说详细点
    2013年1月6日 10:02
  • 是的,记住没有的时候才Add,有的时候直接设置True。

    另外,如果你是COM的UDF,如果Addins中没有,你不能单纯的Add,你可能还需要注册,在Win7下就涉及到UAC,你必须以管理员权限来注册你的UDF组件。

    2013年1月6日 10:08