使用VB区分xlsx事件与c#互操作或Excel程序

Question

我想在用户关闭.xlsx文件时触发一个事件。我也有一个c#程序，定期检查同一文件中的内容。我遇到的问题是，当我的c#程序(使用Interop)关闭.xlsx文件时，它会触发我的VB代码。是否有一种方法可以隔离对excel文件的访问，以便我知道它是在excel程序中编辑的?

我只希望我的VB代码触发时，用户编辑Excel内的文件。

谢谢

Answer 1

我决定简单地让我的c#程序在开始时创建一个临时文件并在结束时删除它。如果VB看到临时文件，这意味着c#一定在编辑excel文件，否则我们可以假设它是一个用户。

Answer 2

我希望你得到一个更好的答案，但我相信如果你没有得到更好的答案，这个答案提供了一个解决方案。

底部是一个宏，它返回活动进程的数组。当我从一个宏调用一个长时间运行的程序，并且我想知道它什么时候结束时，我通常使用这个。我通过每隔几秒钟调用例程来确定这一点，直到程序从数组中消失。

在这个例程之上是一个Workbook_BeforeClose事件宏。这将获取活动进程的列表并将其写入文件。

我写了一个程序来打开和关闭一个Excel工作簿。它是VB。Net而不是c#，但它使用InterOp，所以我怀疑这有什么关系。

我的笔记本电脑上运行着80个左右的进程，所以我只包括了我认为相关的。

如果我通过点击打开工作簿，我得到:

Current  Parent Process
   3396    3252 explorer.exe
   5452    3396 EXCEL.EXE

请注意，EXCEL.EXE的父级是explorer.exe。

如果我用程序打开工作簿，我得到:

Current  Parent Process
    920     760 svchost.exe
   3396    3252 explorer.exe
   5912    3396 OpenCloseExcel.exe
   1056     920 EXCEL.EXE

这里EXCEL.EXE已被svchost.exe打开，我的程序OpenCloseExcel.exe处于活动状态。注意:在进程列表中有许多svchost.exe的副本，但我只包括了相关的副本。

最后，我打开一个不同的工作簿，然后运行OpenCloseExcel.exe。这次我得到:

Current  Parent Process
    920     760 svchost.exe
   3396    3252 explorer.exe
    324    3396 EXCEL.EXE
   5116    3396 OpenCloseExcel.exe
   5108     920 EXCEL.EXE

这里有两个EXCEL.EXE的副本;一个由explorer.exe打开，一个由svchost.exe打开。我的程序再次被列为活动进程。互操作允许程序使用现有的Excel副本(如果有的话)。我没有测试过这种情况。

这似乎提供了确定打开工作簿的两种替代方法:(1)它是由explorer.exe打开的，(2)您的程序是活动的吗?

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim FlOut As Object
  Dim FlSysObj As Object
  Dim InxP As Long
  Dim Process() As ProcessDtl
  Set FlSysObj = CreateObject("Scripting.FileSystemObject")
  Set FlOut = FlSysObj.CreateTextFile( _
                    ActiveWorkbook.Path & "" & Format(Now(), "hhmmss") & ".txt")
  Call GetProcessList(Process)
  FlOut.WriteLine "Current  Parent Process"
  For InxP = LBound(Process) To UBound(Process)
    With Process(InxP)
      FlOut.WriteLine Right$(Space(7) & .IdCrnt, 7) & _
                      Right$(Space(8) & .IdParent, 8) & " " & .Name
    End With
  Next
  FlOut.Close
End Sub

Option Explicit
  ' Source http://vbadud.blogspot.co.uk/2007/06/show-all-processes-using-vba.html
  ' Modified by Tony Dallimore
  Const TH32CS_SNAPHEAPLIST = &H1
  Const TH32CS_SNAPPROCESS = &H2
  Const TH32CS_SNAPTHREAD = &H4
  Const TH32CS_SNAPMODULE = &H8
  Const TH32CS_SNAPALL = (TH32CS_SNAPHEAPLIST Or _
                          TH32CS_SNAPPROCESS Or _
                          TH32CS_SNAPTHREAD Or _
                          TH32CS_SNAPMODULE)
  Const TH32CS_INHERIT = &H80000000
  Const MAX_PATH As Integer = 260
  Private Type PROCESSENTRY32
    dwSize As Long
    cntUsage As Long
    th32ProcessID As Long
    th32DefaultHeapID As Long
    th32ModuleID As Long
    cntThreads As Long
    th32ParentProcessID As Long
    pcPriClassBase As Long
    dwFlags As Long
    szExeFile As String * MAX_PATH
  End Type
  Public Type ProcessDtl
    IdCrnt As Long
    IdParent As Long
    Name As String
  End Type
  Private Declare Function CreateToolhelp32Snapshot Lib "kernel32" _
                      (ByVal lFlags As Long, ByVal lProcessID As Long) As Long
  Private Declare Sub CloseHandle Lib "kernel32" (ByVal hPass As Long)
  ' API Functions to get the processes
  Private Declare Function Process32First Lib "kernel32" _
                      (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
  Private Declare Function Process32Next Lib "kernel32" _
                      (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Public Sub GetProcessList(Process() As ProcessDtl)
  Dim hSnapShot As Long          '* Handle
  Dim uProcess As PROCESSENTRY32 '* Process
  Dim lRet                       '* Return Val
  Dim InxP As Long
  Dim Pos As Long
  ReDim Process(1 To 100)
  InxP = 0      ' Last used entry in array
  ' Takes a snapshot of the running processes and the heaps, modules,
  ' and threads used by the processes
  hSnapShot = CreateToolhelp32Snapshot(TH32CS_SNAPALL, 0&)
  uProcess.dwSize = Len(uProcess)
  ' Retrieve information about the first process encountered in our system snapshot
  ' uProcess.szExeFile is a fixed length string of 260 characters.  Each new process
  ' name is terminated with &H0 and overwrites the previous name.  Hence the need to
  ' discard the first &H0 and any characters that follow.
  ' In the original code, the first process name was ignored.  During my
  ' experimentation, the first name was always "[System Process]" which appears to be
  ' a header.  I continue to discard the first process name but only if it is
  ' "[System Process]"
  ' In the original code, the final lRet was output before being tested to be true.
  ' This meant the last name was junk.  I always test lRet before extracting the name.
  lRet = Process32First(hSnapShot, uProcess)
  If Left$(uProcess.szExeFile, 16) = "[System Process]" Then
    lRet = Process32Next(hSnapShot, uProcess)
  End If
  ' lRet is 0 or 1.  1 means uProcess has been loaded with another process.
  Do While lRet
    InxP = InxP + 1
    If InxP > UBound(Process) Then
      ReDim Preserve Process(1 To UBound(Process) + 100)
    End If
    Process(InxP).IdCrnt = uProcess.th32ProcessID
    Process(InxP).IdParent = uProcess.th32ParentProcessID
    Pos = InStr(1, uProcess.szExeFile, Chr$(0))
    If Pos > 0 Then
      Pos = Pos - 1
    Else
      Pos = 1
    End If
    Process(InxP).Name = Left$(uProcess.szExeFile, Pos)
    lRet = Process32Next(hSnapShot, uProcess)
  Loop
  CloseHandle hSnapShot
  ' This ReDim assumes there is at least one process.
  ReDim Preserve Process(1 To InxP)  ' Discard empty entries
End Sub

Answer 3

从你的问题中不清楚，但在典型的c#互操作背景工作中，实际的Excel应用程序没有显示给用户。1如果这也是你的情况，你可以使用

应用程序。可见属性(Excel)

来区分两种情况。

如果这不是你的情况，那么2你可以从你的c#代码中为你的VBA代码在隐藏工作表的单元格中留下一个message。

使用隐藏表进行很多配置和其他内部临时表是非常有用的。最终用户只是不知道它，但Excel的其余部分(公式和VBA代码)可以正常使用它。