Can't get correct instance of Excel for paste

[rauty]

Hi all,

I'm trying to access the most recent instance of Excel so I can paste data. What works for me is if Excel isn't already opened, I open it and can paste the data where I want to. What isn't working for me is if there are multiple instances of Excel open.

I'll explain: Say I have 3 instances of Excel opened, each with one workbook. Instance 1 = Book1, instance 2 = Book2, and instance 3 = Book3. I want to paste my data to Book3, since that's the most-recently-opened instance. Here's my code:

    ' Get (array of) all running Excel processes
    Dim xlProcesses As Process() = Process.GetProcessesByName("excel")
 
    If xlProcesses.Length = 0 Then                  ' If Excel isn't found
      Dim xL As Process = Process.Start("excel")    ' Open Excel
      Sleep(500)                                    ' Wait for Excel to start before continuing 
    Else
      ' Activate the most-recently-active workbook in the most recent instance of Excel
      '  (processes are listed in the order of most-recently-opened first).
      AppActivate(xlProcesses(0).Id)
      Try
        ' Assign the active Excel process
        xlApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
      Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
      End Try
    End If
    ' Get filename (only, no path) for current window
    xlFileName = xlApp.ActiveWindow.Caption

The AppActivate (line 10) does indeed activate the correct instance of Excel (Book3), but xlApp (line 13) ends up getting assigned to the Book1 instance, and my data ends up pasting there, even though Book3 is the visible/active workbook.

Can somebody please help me understand what's going on here, and how to fix it? I would appreciate some code, because I'm fairly new to .NET and can't always understand if somebody says, "Just use this and this..." Thanks in advance for any and all help. I've been working on this for two days and it's driving me crazy trying to figure out what's going on!

[Plater]

Do you actually have three different excel.exe's running, or are there just 3 different windows open?
If there were 3 Excel.exe's running, you could examine the Process.StartTime of them for the newest opened?

I am unfamiliar with what you are doing for the Marshal, so I looked it up:

Marshal.GetActiveObject exposes the GetActiveObject COM API method from OLEAUT32.DLL; however, the latter expects a class identifier (CLSID) instead of the programmatic identifier (ProgID) expected by this method. To obtain a running instance of a COM object without a registered ProgID, use platform invoke to define the GetActivateObject COM method. For a description of platform invoke, see Consuming Unmanaged DLL Functions. For additional information about the GetActiveObject COM method, see the MSDN Library.

You may have to use the P/Invoke for this

[rauty]

I have 3 unique instances of Excel open (3 separate entries in Task Manager). I know how to handle multiple windows in 1 Excel instance.

I originally was looking at the Process.StartTime in a loop before I did my AppActivate, but after observing the order that the ProcessIDs were listed, I saw that the most recent instance was always listed first, and then the next-most-recent, etc. That's why I cleaned up the code and am just taking the initial (0) instance.

As I stated in my previous note, I am indeed activating the correct process with AppActivate, since that's the window that pops to the foreground (and the desired ProcID is returned). However, when I try to assign that instance to an Excel.Application object, it assigns the oldest (first-opened) instance instead, and not the one that's actually active. Wacky.

[Plater]

Well you don't actually do anything with the process or process IDs in your Marshall call, you just tell it to get an instance of "Excell.Application", which my guess would be returns the oldest instance (aka the first instance).
I think you will have to find a way to pass in the exact progid string for that excell instance, or look into using the pinvoke call.

Or maybe there is some functions already built into the msoffice.interop you could use?

[rauty]

Hmmm... I was assuming that once the process was activated a la AppActivate then GetActiveObject would just pick it up (since it was the active object). Obviously that was wrong. :^D

I've never heard of P/Invoke before, and it seems that it's a C# command. I'm actually using VB.NET for programming. I'm sorry I didn't specify that before, but I thought people might be able to tell by the code.

I found the Marshal.GetActiveObject command on another forum and tried to use it. It actually does work, but only if there's only one instance of Excel running.

Anyway, I wonder if I can do something with GetForegroundWindow? If I get a window handle to the Excel instance, can I somehow get the filename from that and then just use GetObject to grab it? This is where I'm stuck. As my title above says, I'm a newbie to VB.NET. I've been using VBA in Excel for many years, and this is my first venture into creating a Windows application, so I still have a lot to learn. I do appreciate your advice, even though I really don't understand the P/Invoke stuff. As I said, it would be easier for me to see code than to just read "Use this command", since I really don't know HOW to use it!

[Curtis Rutland]

P/Invoke is just another way of saying use DllImport to call the Win32 API methods.

However, I have no experience in the question you're asking, so I can't just hand you sample code :(

[Plater]

All AppActivate seems to do is make a process be the active process.
The two calls don't seem to interact. Its not even a real .NET call, its from an older VB library, wheras marshalling is .NET.
Ignoring for a moment the DLLImport stuff.

Does you project have a reference to the msoffice dlls? I think they must in order for you to be using the Excell.Application object.
I think somewhere in that DLL/library there is/should be a function that will grab object instances of running excel processes. You should be able to do something with that then.
Although I haven't done it, so I cannot tell any better where to look other then to dig around in the msoffice library.

[rauty]

Thanks for your replies!

Here are the namespaces I'm using in this module:

Imports System.Runtime.InteropServices
Imports System.Threading.Thread
Imports Microsoft.Office.Core
Imports Excel

As I said, I'm pretty new to .NET programming so I have no idea how I can "dig around in the msoffice library". I'm a mechanical engineer and create Excel macros and add-ins on the side using VBA and this is my first venture into Windows application programming. I'm using VB 2008 Express Edition and have been digging through the online help, which, I must say, isn't very helpful a lot of the time. Most of the time it'll bring up C# or other .NET language help, which I can't use. I know many of the methods and functions are the same, but the syntax isn't, and that's where I can get bogged down. I haven't been able to determine how to filter the help to only show VB.NET help either. Maybe I need to only look at the local help and not the online?

Anyway, I thought that this would be an easy fix for somebody with more experience. I may just have to break down and pop up a messagebox telling the user to close all other instances of Excel except the one they want to paste to, but I'd really rather not have to do that if I don't have to.

Again, I do appreciate all of the replies and if somebody else can help, please jump in. I may also post on one of the MSDN forums and see if anybody there can help me.

[Plater]

At the top of the page there should be a little language filter (msdn.com and the local library) that lets you pick which example languages to show syntax for.

And by poking around i mean looking at what objects are available in the
Microsoft.Office namespace(s).

I looked a bit online, and you might be able to do something with the 3rd vb example here:
Access running instances of Excel in VB « VSTO & .NET & Excel

[rauty]

Thanks for the link, Plater.

That seems to be along the lines of what I'm looking for, but that approach seems to depend on knowing the filename of the workbook you want to access. However, in my case I have no idea what that the filename of the active workbook might be. Basically this application will be used on a public workstation and I'm assuming that the active workbook in the most recent instance of Excel is the one that the user will want to paste to (i.e., the user opens the workbook and then hits the paste button in my application). Through AppActivate, when the most-recent version of Excel is activated then the most-recently active workbook is automatically shown. It would be nice if I could somehow get a pointer or the filename of that workbook, then I could either use GetObject or BindToMoniker to go from there. This is where I've been stuck. What I was trying to do is to "take this process ID and assign it to an Excel.Application object", which is what I was assuming I was doing initially.

Ideally, it might be nice to access all of the open workbooks in all of the instances of Excel, then I could just display them to the user and they could pick which workbook they wanted to paste to. But the way my code is now, I can only access the workbooks in the (incorrect) active instance of Excel, and not all of the open workbooks in all instances.

[Ramk]

You can create a small com component in VC/VB which can utilize the small platform SDK function summarized below. After this, you can
import to your .net code to get the latest active excel application.

HWND g_hExcel = NULL;
BOOL FindLatestExcel(HWND hwnd, LPARAM lParam)
{ 
 CWnd* pWnd = CWnd::FromHandle(hwnd);
 if(pWnd)
 {
  CString str = "";
  pWnd->GetWindowText(str);
  if(-1 != str.Find("Microsoft Excel"))
  {
   //AfxMessageBox(str);
   g_hExcel = hwnd;
   return FALSE;
  }
 }
 return TRUE;
}
long CRunningExcelCtrl::GetExcelInstanceHandle() 
{
 EnumDesktopWindows(NULL, (WNDENUMPROC)FindLatestExcel, 0L);//;//EnumWindows((WNDENUMPROC)FindLatestExcel, 0L); 
 
 // TODO: Add your dispatch handler code here
 LONG retval = (LONG)g_hExcel;
 return retval;
}

In your .net code,
you can use
Process[] procs = Process.GetProcessesByName("EXCEL");
foreach (Process p in procs)
{
if (p.MainWindowHandle.ToInt64() == g_lVCReturnedHandle)
 {
   // Do paste your code in the excel sheet.
 }
}
 

Let me know whether this can helps you!!!

[rauty]

Ramk,

Thanks for your reply; I appreciate your help. Can you give me the code in VB.NET? I really can't follow the code you gave me. Is that C? C++? As I said, I'm a newbie and not gifted in many programming languages.

If you can't convert it, I might be able to dig up somebody on my end who can, but it would be much easier if you (or somebody else reading this) could provide it for me. Let me know.

[nukefusion]

The top sample is intended to be code for a small COM component, hence why it is written in C++, however I'm not sure it will help you, as after quickly scanning the code it looks like it will only return a window handle for the most recent version of Excel and not the actual application object.
From what you have said and the code sample you posted, you can get this via .NET already.

This is probably not what you want to hear, but it's actually NOT particularly easy to do what you want. There are really two ways to get a COM object in .NET. You can use Marshal.GetActiveObject, which you've already done. This will always return the first opened copy of Excel registered in the Running Object Table (ROT). That's not really much use to you.

The other option, if you need to return a specific instance, and you know the name of the open workbook, is to use Marshal.BindToMoniker.
Use it in the same way as GetActiveObject. You'll need to pass in the full file path of the open workbook you want to bring to the foreground.

Really, in all truth, if you want to add it to the newest open copy of Excel it would be far easier to just create the new Excel object in your application and deal with it from there.

[rauty]

Hi nukefusion,

Thanks for your input. Yeah, it seems like it's getting to be pretty much a lost cause. Opening up a new instance of Excel for the paste is a good idea, but in my case it wouldn't work. My users are scanning printed documents and pasting the processed data into different places on a single spreadsheet.

What I've done now is if there are multiple instances of Excel I just pop up a messagebox and tell the user to close all other instances except the one they wish to paste to, which works, but if somebody else has a workbook open in another instance that they haven't saved, it might get blown away by mistake.

Anyway, thanks to all for your ideas. I do appreciate them. Unless somebody else can come up with a working solution it appears we're done here. I do have another thread open on MSDN forums but so far it appears that we're coming to the same conclusion there also.

Have a great day!

[Ramk]

Im working on your sample. I will post the code little later.
Infact, be patient.

[rauty]

Thanks for taking the time to work on it!

[Plater]

I had a thought, it might be a bit of a leap though.
Basically its this:

Given:
You can get an exact instance of Excell, if you know the workbook that is open in it.
You can find the Process P of the most recently opened excel file.

Task:
Using .NET Process (or more likely win32_api calls on processes) you can determine what files are open in a program. You can also use the window title of the excel to give a starting point.
Window title for most recent Excel process P "Microsoft Excel Book2"
Now dig through list of open files for a match (of some sort) for "Book2"
Then you have the "open workbook" of the excell process you want and should be able to open right?
Maybe that could work?

[rauty]

Hi again Plater,

You are correct, and that's been my dilemma: I've just activated the correct instance of Excel, and it's sitting there open and looking at me all pretty and stuff. Now how can I get the name of the open workbook, or at least the window caption? If I knew that, then I maybe could do a Marshall.BindToMoniker or something similar and assign it to the Excel.Application object and I'd be off to the races.

That's why I think I've been so close. If I only had that bit of info I'd be golden.

[Plater]

these folks seem to have some help on that part:
How do I get the list of open file handles by process in C#? - Stack Overflow

[rauty]

Thanks! I'll take a look at it.

[Ramk]

Hi I have written some code for you. Here you can the caption also.
Sorry. I was late as Im so busy in last couple of days.

This is main code used in getting the excel sheet. Here you can get the caption of it also.

'Option Explicit On
Imports System.Text
Public Class Class1
    ' Callback delegates
    Private Delegate Function EnumWindowCallbackDelegate _
        (ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
    ' Win32 API's
    Private Declare Function EnumWindows Lib "user32" _
        (ByVal lpEnumFunc As EnumWindowCallbackDelegate, ByVal lParam As IntPtr) As Boolean
    Private Declare Auto Function GetWindowTextLength Lib "user32" _
        (ByVal hWnd As IntPtr) As Integer
    Private Declare Auto Function GetWindowText Lib "user32" _
        (ByVal hWnd As IntPtr, ByVal lpString As StringBuilder, ByVal nMaxCount As Integer) As Integer
    Private Declare Function IsWindowVisible Lib "user32" _
        (ByVal hWnd As IntPtr) As Boolean
    Private Declare Function GetParent Lib "user32" _
        (ByVal hWnd As IntPtr) As IntPtr
    ' For later use...
    ' Holds the last open excel handle
    Dim excelPtr As IntPtr = IntPtr.Zero
    Public Function GetLastActiveExcelHandle() As IntPtr
        Dim enumProcDelegate As New EnumWindowCallbackDelegate(AddressOf EnumWindowsCallbackFunction)
        EnumWindows(enumProcDelegate, excelPtr)
        If (excelPtr <> IntPtr.Zero) Then
            MsgBox("Some excel files are opened")
        Else : MsgBox("No excel file is opened")
        End If
        Return excelPtr
    End Function
    Private Function EnumWindowsCallbackFunction(ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
        If IsWindowVisible(hWnd) And GetParent(hWnd) = IntPtr.Zero Then
            Dim buffer As StringBuilder = New StringBuilder(GetWindowTextLength(hWnd) + 1)
            GetWindowText(hWnd, buffer, buffer.Capacity)
            Dim windowText As String = buffer.ToString()
            If windowText.Contains("Microsoft Excel") Then
                MsgBox(windowText)
                excelPtr = hWnd
                Return False
            End If
        End If
        Return True
    End Function
End Class
 

Once you have the handle, you can return it to the main program along with other data also(if you require). It looks like

    Dim myref As ClassLibrary1.Class1 = New ClassLibrary1.Class1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Get the latest excel handle.
        Dim ret As IntPtr = myref.GetLastActiveExcelHandle()
        Dim myExcelProcesses As Process() = Process.GetProcessesByName("Excel")
        For Each xlProcess As Process In myExcelProcesses
            If (ret = xlProcess.MainWindowHandle) Then
                ' Do paste the code here.
                ' Get (array of) all running Excel processes 
                'Dim xlProcesses As Process() = Process.GetProcessesByName("excel")
                If myExcelProcesses.Length = 0 Then               ' If Excel isn't found 
                    Dim xL As Process = Process.Start("excel")    ' Open Excel 
                    'Sleep(500)                                   ' Wait for Excel to start before continuing  
                Else
                    ' Activate the most-recently-active workbook in the most recent instance of Excel 
                    '  (processes are listed in the order of most-recently-opened first). 
                    AppActivate(xlProcess.Id)
                    'Try
                    '    ' Assign the active Excel process 
                    '    xlApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application))
                    'Catch ex As Exception
                    '    MsgBox(ex.Message)
                    '    Exit Sub
                    'End Try
                End If
                ' Get filename (only, no path) for current window 
                'xlFileName = xlApp.ActiveWindow.Caption
            End If
        Next xlProcess
    End Sub
End Class

Let me know if you require any other help.Happy coding.

[rauty]

Ramk,

Wow, thanks so much for the code! I can't wait to try it out. I've been working on another application for the last few days, but I'll try to get to it soon and let you know how it goes.

[rauty]

Hi all,

Just to let you know that I haven't forgotten about you. I've been working on another application and it's been taking up all of my time. I hope to be finished with it soon so I can try out Ramk's code. Thanks again for all of the help!

[rauty]

Hi Ramk,

Well, I finally finished up that other project that was tying up all of my time, and now I've got time to focus back on the application you had supplied me code for.

I created a new class and pasted the class code you gave me, as follows:

Imports System.Text
 
Public Class Class1
 
  ' Callback delegates
  Private Delegate Function EnumWindowCallbackDelegate(ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
 
  ' Win32 API's
  Private Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As EnumWindowCallbackDelegate, ByVal lParam As IntPtr) As Boolean
  Private Declare Auto Function GetWindowTextLength Lib "user32" (ByVal hWnd As IntPtr) As Integer
  Private Declare Auto Function GetWindowText Lib "user32" (ByVal hWnd As IntPtr, ByVal lpString As StringBuilder, ByVal nMaxCount As Integer) As Integer
  Private Declare Function IsWindowVisible Lib "user32" (ByVal hWnd As IntPtr) As Boolean
  Private Declare Function GetParent Lib "user32" (ByVal hWnd As IntPtr) As IntPtr
 
  ' For later use...
  ' Holds the last open excel handle
  Dim excelPtr As IntPtr = IntPtr.Zero
 
  Public Function GetLastActiveExcelHandle() As IntPtr
 
    Dim enumProcDelegate As New EnumWindowCallbackDelegate(AddressOf EnumWindowsCallbackFunction)
    EnumWindows(enumProcDelegate, excelPtr)
    If (excelPtr <> IntPtr.Zero) Then
      MsgBox("Some excel files are opened")
    Else : MsgBox("No excel file is opened")
    End If
    Return excelPtr
 
  End Function
 
  Private Function EnumWindowsCallbackFunction(ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
 
    If IsWindowVisible(hWnd) And GetParent(hWnd) = IntPtr.Zero Then
      Dim buffer As StringBuilder = New StringBuilder(GetWindowTextLength(hWnd) + 1)
      GetWindowText(hWnd, buffer, buffer.Capacity)
      Dim windowText As String = buffer.ToString()
      If windowText.Contains("Microsoft Excel") Then
        MsgBox(windowText)
        excelPtr = hWnd
        Return False
      End If
    End If
    Return True
 
  End Function
 
End Class

Then I merged the remaining code into the sub where my paste code was, thus:

  Sub ExcelPaste()
 
    ' Gets cell info in the paste range for undo, then pastes textbox data
 
    Dim FirstCrLf As UShort = 0
    Dim TextboxText As String
    Dim I As UShort
    Dim NumCols As UInteger = 1
    Dim NumRows As UInteger = 0
    Dim strReader As New StringReader(TSForm.tboxResults.Text)
    Dim RowText As String = ""
    Dim ThisCell As String
 
    ' Get the latest excel handle. 
    Dim ret As IntPtr = myref.GetLastActiveExcelHandle()
    Dim myExcelProcesses As Process() = Process.GetProcessesByName("Excel")
    For Each xlProcess As Process In myExcelProcesses
      If (ret = xlProcess.MainWindowHandle) Then
 
        ' Determine how much text to paste
        If TSForm.tboxResults.SelectionLength = 0 Then
          TextboxText = TSForm.tboxResults.Text
          Clipboard.SetDataObject(TSForm.tboxResults.Text)
        Else
          TextboxText = TSForm.tboxResults.SelectedText
          Clipboard.SetDataObject(TSForm.tboxResults.SelectedText)
        End If
 
        ' Find end of first line
        FirstCrLf = InStr(TextboxText, Chr(13))
        ' Count number of tab characters in first line = number of columns
        For I = 1 To FirstCrLf
          If Mid(TextboxText, I, 1) = Chr(9) Then NumCols += 1
        Next
 
        ' Count number of rows
        Do
          RowText = strReader.ReadLine()
          If RowText Is Nothing Then Exit Do
          NumRows += 1
        Loop Until (RowText Is Nothing)
        strReader.Close()
 
        ' Get existing cell data (for undo), then paste textbox data
        With xlApp
          .Windows(xlFileName).Activate()
          ThisCell = .ActiveCell.Address
          ' Get current data for undo
          PasteRange = .Range(ThisCell, .Cells(.ActiveCell.Row + NumRows - 1, .ActiveCell.Column + NumCols - 1))
          ReDim CellValues(NumRows - 1, NumCols - 1)
          CellValues = PasteRange.Formula
          ' Paste data
          .ActiveSheet.PasteSpecial(Format:="Text")
        End With
        If myExcelProcesses.Length = 0 Then             ' If Excel isn't found  
          Dim xL As Process = Process.Start("excel")    ' Open Excel  
          'Sleep(500)                                   ' Wait for Excel to start before continuing
        Else
          AppActivate(xlProcess.Id)
        End If
      End If
    Next xlProcess
 
    TSForm.btnExcelUndo.Enabled = True
 
  End Sub

However, when I run the application, the paste code doesn't work. Instead, I just get messageboxes that pop up with various messages from Class1; nothing else happens. The code fails once it enters the 'With xlApp' routine (line 45). Can you please help me out with this? I would appreciate it!

[rauty]

Hi Ramk,

I forgot to paste all of my code into the previous eMail. The lower code block should read as follows:

Dim myref As Class1 = New Class1
 
  Sub ExcelPaste()
 
    ' Gets cell info in the paste range for undo, then pastes textbox data
 
    Dim FirstCrLf As UShort = 0
    Dim TextboxText As String
    Dim I As UShort
    Dim NumCols As UInteger = 1
    Dim NumRows As UInteger = 0
    Dim strReader As New StringReader(TSForm.tboxResults.Text)
    Dim RowText As String = ""
    Dim ThisCell As String
 
    ' Get the latest excel handle. 
    Dim ret As IntPtr = myref.GetLastActiveExcelHandle()
    Dim myExcelProcesses As Process() = Process.GetProcessesByName("Excel")
    For Each xlProcess As Process In myExcelProcesses
      If (ret = xlProcess.MainWindowHandle) Then
 
        ' Determine how much text to paste
        If TSForm.tboxResults.SelectionLength = 0 Then
          TextboxText = TSForm.tboxResults.Text
          Clipboard.SetDataObject(TSForm.tboxResults.Text)
        Else
          TextboxText = TSForm.tboxResults.SelectedText
          Clipboard.SetDataObject(TSForm.tboxResults.SelectedText)
        End If
 
        ' Find end of first line
        FirstCrLf = InStr(TextboxText, Chr(13))
        ' Count number of tab characters in first line = number of columns
        For I = 1 To FirstCrLf
          If Mid(TextboxText, I, 1) = Chr(9) Then NumCols += 1
        Next
 
        ' Count number of rows
        Do
          RowText = strReader.ReadLine()
          If RowText Is Nothing Then Exit Do
          NumRows += 1
        Loop Until (RowText Is Nothing)
        strReader.Close()
 
        ' Get existing cell data (for undo), then paste textbox data
        With xlApp
          .Windows(xlFileName).Activate()
          ThisCell = .ActiveCell.Address
          ' Get current data for undo
          PasteRange = .Range(ThisCell, .Cells(.ActiveCell.Row + NumRows - 1, .ActiveCell.Column + NumCols - 1))
          ReDim CellValues(NumRows - 1, NumCols - 1)
          CellValues = PasteRange.Formula
          ' Paste data
          .ActiveSheet.PasteSpecial(Format:="Text")
        End With
        If myExcelProcesses.Length = 0 Then             ' If Excel isn't found  
          Dim xL As Process = Process.Start("excel")    ' Open Excel  
          'Sleep(500)                                   ' Wait for Excel to start before continuing
        Else
          AppActivate(xlProcess.Id)
        End If
      End If
    Next xlProcess
 
    TSForm.btnExcelUndo.Enabled = True
 
  End Sub