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

I have an event that I want triggered when a user closes a .xlsx file. I also have a C# program that regularly checks the content within that same file. The problem I have is that when my C# program (which uses Interop) closes the .xlsx file, it triggers my VB code. Is there a way to isolate access to the excel file so that I know it was edited within the Excel program?

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

I only want my VB code triggered when a user edits the file within Excel.

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

Thanks

谢谢

3 个解决方案

#1

I decided to simply have my C# program create a temp file at the beginning and delete it at the end. If VB sees the temp file, it means that C# must be editing the excel file, otherwise we can assume it is a user.

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

#2

I hope you get a better answer but I believe this answer offers a solution if you don't get anything better.

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

At the bottom is a macro that returns an array of the active processes. I normally use this when I have called a long-running program from a macro and I want to know when it has finished. I determine this by calling the routine every few seconds until the program disappears from the array.

底部是一个宏，它返回活动进程的数组。当我从一个宏调用一个长时间运行的程序并想知道它何时完成时，我通常使用这个函数。我每隔几秒钟调用一次例程，直到程序从数组中消失。

Above that routine is a Workbook_BeforeClose event macro. This gets a list of the active processes and writes then to a file.

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

I have written a program that open and closes an Excel workbook. It is VB.Net rather than C# but it uses the InterOp so I doubt that matters.

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

I have 80 or so processes running on my laptop so I have only included the ones I consider relevant.

我有大约80个进程在我的笔记本上运行，所以我只包含那些我认为相关的进程。

If I open the workbook by clicking it, I get:

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

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

Notice that EXCEL.EXE's parent is explorer.exe.

注意到EXCEL。EXE的母公司是资源管理器。

If I open the workbook with my program, I get:

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

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

Here EXCEL.EXE has been opened by svchost.exe and my program, OpenCloseExcel.exe, is active. Note: there are many copies of svchost.exe in the process list but I have only included the relevant copy.

EXCEL。EXE已由svchost打开。exe和我的程序，OpenCloseExcel。exe是活跃的。注:svchost有很多副本。exe在流程列表中，但我只包含了相关的副本。

Finally, I opened a different workbook and then ran OpenCloseExcel.exe. This time I get:

最后，我打开另一个工作簿，然后运行opencloseexce .exe。这一次我得到:

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

Here there are two copies of EXCEL.EXE; one opened by explorer.exe and one opened by svchost.exe. Again my program is listed as an active process. The InterOp allows a program to use an existing copy of Excel if there is one. I have not tested this situation.

这里有两份EXCEL.EXE;开了一个探险家。exe和一个由svchost.exe打开。我的程序再次被列为活动进程。InterOp允许程序使用已有的Excel副本(如果有的话)。我还没有测试过这种情况。

This appears to offer two alternatives ways of determining what opened your workbook: (1) was it opened by explorer.exe and (2) is your program active?

这似乎提供了两种方法来确定什么打开了你的工作簿:(1)它是由资源管理器打开的。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

#3

It is not clear from your question but in typical C# interop background work the actual Excel application is not shown to the user. 1 If this is also your case you can use

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

Application.Visible Property (Excel)

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

to distinguish between the two cases.

区分这两种情况。

If this is not your case then 2 you can leave a message from your C# code for your VBA code in a cell in a hidden sheet.

如果这不是您的情况，那么2，您可以在隐藏表的单元格中为您的VBA代码留下一条来自c#代码的消息。

Using hidden sheets for lots of configuration things and other internal temporary tables is quite useful. End user is just not aware of it but the rest of Excel (both formulas and VBA code) can use it as normal.

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