解决方案，以查看Excel是否在.NET中处于单元格编辑模式 [英] Workaround to see if Excel is in cell-edit mode in .NET

问题描述

我有一个用VB.NET编写的应用程序，可以通过互操作与Excel进行交互. 我最终遇到了单元格编辑模式的已知问题(请参阅 MSDN 和堆栈溢出(针对某些背景).

I have an application written in VB.NET that interacts with Excel via interop. I eventually ran into the known issue of Cell-edit mode (see MSDN and stackoverflow for some background).

我一直在尝试将建议的代码转换为VB.NET，但始终出现以下错误:

I have been trying to convert the suggested code to VB.NET but keep getting the following error:

Reference required to assembly 'office, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' containing the type 'Microsoft.Office.Core.CommandBars'. Add one to your project. (BC30652) - E:\ ... .vb:3471

原始C#代码(来自之前提到的文章)如下

The original C# code (from previosuly mentioned articles) is as follows

private bool IsEditMode()
{
   object m = Type.Missing;
   const int MENU_ITEM_TYPE = 1;
   const int NEW_MENU = 18;

   // Get the "New" menu item.
   CommandBarControl oNewMenu = Application.CommandBars["Worksheet Menu Bar"].FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, true );

  if ( oNewMenu != null )
  {
     // Check if "New" menu item is enabled or not.
     if ( !oNewMenu.Enabled )
     {
        return true;
     }
  }
  return false;
}

我转换后的VB.NET代码如下

My converted VB.NET code is as follows

Private Function isEditMode() As Boolean
    isEditMode = False
    Dim m As Object = Type.Missing
    Const  MENU_ITEM_TYPE As Integer = 1
    Const  NEW_MENU As Integer = 18

    Dim oNewMenu As Office.CommandBarControl
    ' oExcel is the Excel Application object 
    ' the error is related to the below line
    oNewMenu = oExcel.CommandBars("Worksheet Menu Bar").FindControl(MENU_ITEM_TYPE, NEW_MENU, m, m, True)
    If oNewMenu IsNot Nothing Then
        If Not oNewMenu.Enabled Then
            isEditMode = True
        End If
    End If
End Function

我已经添加了(COM)对Microsoft Office对象库的引用

I have added a (COM) reference to the Microsoft Office Object Library

Imports Office = Microsoft.Office.Core
Imports Microsoft.Office.Interop

我有点被困住了.我已经尝试过间接引用CommandBar对象，并重新添加引用，但无法弄清楚是什么问题.有任何想法吗?

I am kind of stuck. I already have tried indirectly referencing the CommandBar object, and re-adding refrences but can not figure out what is the problem. any ideas?

推荐答案

作为快速修复程序，我使用以下代码作为替代方法

As a quick-and-dirty fix i used the following code as an alternative

Private Function isEditMode() As Boolean
    isEditMode = False
    Try
        oExcel.GoTo("###")
    Catch Ex As Exception
       ' Either returns "Reference is not valid." 
       ' or "Exception from HRESULT: 0x800A03EC"
       If ex.Message.StartsWith("Exception") then isEditMode  = True
    End Try     
End Function

当Excel处于单元格编辑"模式时，.GoTo函数(和相应的菜单项)不可用. 如果代码运行时用户在单元格中工作，赋予.GoTo函数一个虚拟目标位置将不会执行任何操作，也不会影响任何操作.

The .GoTo function (and corresponding menu item) is not available when Excel is in Cell-edit mode. Giving the .GoTo function a dummy destination will do nothing and won't affect anything if the user is working in the cell when the code runs.

一个额外的好处是不需要引用Microsoft Office对象(Microsoft.Office.Core)库.

An added extra is that no reference to the Microsoft Office Object (Microsoft.Office.Core) library is needed.

这篇关于解决方案，以查看Excel是否在.NET中处于单元格编辑模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！