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.StartTi me of them for the newest opened?

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

Marshal.GetActi veObject 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 GetActivateObje ct 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.StartTi me 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.Applicati on 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.Applica tion", 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.intero p 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.GetActi veObject 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 GetForegroundWi ndow? 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.Applicat ion 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.Threadin g.Thread
Imports Microsoft.Offic e.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.Offic e 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.Applicati on 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.GetActi veObject, 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.BindToM oniker.
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.