Use VB to distinguish between an xlsx event from C# Interop or Excel program

Question

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?

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

Thanks

Answer 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.

Answer 2

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

Application.Visible Property (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.

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.

Answer 3

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.

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.

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

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.

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.

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

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.

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?

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