By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,018 Members | 878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,018 IT Pros & Developers. It's quick & easy.

Can't get correct instance of Excel for paste

rauty
P: 16
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:

Expand|Select|Wrap|Line Numbers
  1.     ' Get (array of) all running Excel processes
  2.     Dim xlProcesses As Process() = Process.GetProcessesByName("excel")
  3.  
  4.     If xlProcesses.Length = 0 Then                  ' If Excel isn't found
  5.       Dim xL As Process = Process.Start("excel")    ' Open Excel
  6.       Sleep(500)                                    ' Wait for Excel to start before continuing 
  7.     Else
  8.       ' Activate the most-recently-active workbook in the most recent instance of Excel
  9.       '  (processes are listed in the order of most-recently-opened first).
  10.       AppActivate(xlProcesses(0).Id)
  11.       Try
  12.         ' Assign the active Excel process
  13.         xlApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)
  14.       Catch ex As Exception
  15.         MsgBox(ex.Message)
  16.         Exit Sub
  17.       End Try
  18.     End If
  19.     ' Get filename (only, no path) for current window
  20.     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!
Dec 3 '08 #1
Share this Question
Share on Google+
24 Replies


Plater
Expert 5K+
P: 7,872
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
Dec 3 '08 #2

rauty
P: 16
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.
Dec 3 '08 #3

Plater
Expert 5K+
P: 7,872
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?
Dec 3 '08 #4

rauty
P: 16
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!
Dec 3 '08 #5

Curtis Rutland
Expert 2.5K+
P: 3,256
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 :(
Dec 3 '08 #6

Plater
Expert 5K+
P: 7,872
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.
Dec 3 '08 #7

rauty
P: 16
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.
Dec 4 '08 #8

Plater
Expert 5K+
P: 7,872
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
Dec 4 '08 #9

rauty
P: 16
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.
Dec 5 '08 #10

P: 61
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.

Expand|Select|Wrap|Line Numbers
  1. HWND g_hExcel = NULL;
  2. BOOL FindLatestExcel(HWND hwnd, LPARAM lParam)
  3.  CWnd* pWnd = CWnd::FromHandle(hwnd);
  4.  if(pWnd)
  5.  {
  6.   CString str = "";
  7.   pWnd->GetWindowText(str);
  8.   if(-1 != str.Find("Microsoft Excel"))
  9.   {
  10.    //AfxMessageBox(str);
  11.    g_hExcel = hwnd;
  12.    return FALSE;
  13.   }
  14.  }
  15.  return TRUE;
  16. }
  17. long CRunningExcelCtrl::GetExcelInstanceHandle() 
  18. {
  19.  EnumDesktopWindows(NULL, (WNDENUMPROC)FindLatestExcel, 0L);//;//EnumWindows((WNDENUMPROC)FindLatestExcel, 0L); 
  20.  
  21.  // TODO: Add your dispatch handler code here
  22.  LONG retval = (LONG)g_hExcel;
  23.  return retval;
  24. }
Expand|Select|Wrap|Line Numbers
  1. In your .net code,
  2. you can use
  3. Process[] procs = Process.GetProcessesByName("EXCEL");
  4. foreach (Process p in procs)
  5. {
  6. if (p.MainWindowHandle.ToInt64() == g_lVCReturnedHandle)
  7.  {
  8.    // Do paste your code in the excel sheet.
  9.  }
  10. }
  11.  
Let me know whether this can helps you!!!
Dec 9 '08 #11

rauty
P: 16
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.
Dec 9 '08 #12

nukefusion
Expert 100+
P: 221
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.
Dec 9 '08 #13

rauty
P: 16
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!
Dec 9 '08 #14

P: 61
Im working on your sample. I will post the code little later.
Infact, be patient.
Dec 11 '08 #15

rauty
P: 16
Thanks for taking the time to work on it!
Dec 11 '08 #16

Plater
Expert 5K+
P: 7,872
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?
Dec 12 '08 #17

rauty
P: 16
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.
Dec 12 '08 #18

Plater
Expert 5K+
P: 7,872
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
Dec 12 '08 #19

rauty
P: 16
Thanks! I'll take a look at it.
Dec 12 '08 #20

P: 61
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.

Expand|Select|Wrap|Line Numbers
  1. 'Option Explicit On
  2. Imports System.Text
  3. Public Class Class1
  4.     ' Callback delegates
  5.     Private Delegate Function EnumWindowCallbackDelegate _
  6.         (ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
  7.     ' Win32 API's
  8.     Private Declare Function EnumWindows Lib "user32" _
  9.         (ByVal lpEnumFunc As EnumWindowCallbackDelegate, ByVal lParam As IntPtr) As Boolean
  10.     Private Declare Auto Function GetWindowTextLength Lib "user32" _
  11.         (ByVal hWnd As IntPtr) As Integer
  12.     Private Declare Auto Function GetWindowText Lib "user32" _
  13.         (ByVal hWnd As IntPtr, ByVal lpString As StringBuilder, ByVal nMaxCount As Integer) As Integer
  14.     Private Declare Function IsWindowVisible Lib "user32" _
  15.         (ByVal hWnd As IntPtr) As Boolean
  16.     Private Declare Function GetParent Lib "user32" _
  17.         (ByVal hWnd As IntPtr) As IntPtr
  18.     ' For later use...
  19.     ' Holds the last open excel handle
  20.     Dim excelPtr As IntPtr = IntPtr.Zero
  21.     Public Function GetLastActiveExcelHandle() As IntPtr
  22.         Dim enumProcDelegate As New EnumWindowCallbackDelegate(AddressOf EnumWindowsCallbackFunction)
  23.         EnumWindows(enumProcDelegate, excelPtr)
  24.         If (excelPtr <> IntPtr.Zero) Then
  25.             MsgBox("Some excel files are opened")
  26.         Else : MsgBox("No excel file is opened")
  27.         End If
  28.         Return excelPtr
  29.     End Function
  30.     Private Function EnumWindowsCallbackFunction(ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
  31.         If IsWindowVisible(hWnd) And GetParent(hWnd) = IntPtr.Zero Then
  32.             Dim buffer As StringBuilder = New StringBuilder(GetWindowTextLength(hWnd) + 1)
  33.             GetWindowText(hWnd, buffer, buffer.Capacity)
  34.             Dim windowText As String = buffer.ToString()
  35.             If windowText.Contains("Microsoft Excel") Then
  36.                 MsgBox(windowText)
  37.                 excelPtr = hWnd
  38.                 Return False
  39.             End If
  40.         End If
  41.         Return True
  42.     End Function
  43. End Class
  44.  
Once you have the handle, you can return it to the main program along with other data also(if you require). It looks like
Expand|Select|Wrap|Line Numbers
  1.     Dim myref As ClassLibrary1.Class1 = New ClassLibrary1.Class1
  2.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  3.         ' Get the latest excel handle.
  4.         Dim ret As IntPtr = myref.GetLastActiveExcelHandle()
  5.         Dim myExcelProcesses As Process() = Process.GetProcessesByName("Excel")
  6.         For Each xlProcess As Process In myExcelProcesses
  7.             If (ret = xlProcess.MainWindowHandle) Then
  8.                 ' Do paste the code here.
  9.                 ' Get (array of) all running Excel processes 
  10.                 'Dim xlProcesses As Process() = Process.GetProcessesByName("excel")
  11.                 If myExcelProcesses.Length = 0 Then               ' If Excel isn't found 
  12.                     Dim xL As Process = Process.Start("excel")    ' Open Excel 
  13.                     'Sleep(500)                                   ' Wait for Excel to start before continuing  
  14.                 Else
  15.                     ' Activate the most-recently-active workbook in the most recent instance of Excel 
  16.                     '  (processes are listed in the order of most-recently-opened first). 
  17.                     AppActivate(xlProcess.Id)
  18.                     'Try
  19.                     '    ' Assign the active Excel process 
  20.                     '    xlApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application))
  21.                     'Catch ex As Exception
  22.                     '    MsgBox(ex.Message)
  23.                     '    Exit Sub
  24.                     'End Try
  25.                 End If
  26.                 ' Get filename (only, no path) for current window 
  27.                 'xlFileName = xlApp.ActiveWindow.Caption
  28.             End If
  29.         Next xlProcess
  30.     End Sub
  31. End Class
Let me know if you require any other help.Happy coding.
Dec 16 '08 #21

rauty
P: 16
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.
Dec 16 '08 #22

rauty
P: 16
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!
Dec 23 '08 #23

rauty
P: 16
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:

Expand|Select|Wrap|Line Numbers
  1. Imports System.Text
  2.  
  3. Public Class Class1
  4.  
  5.   ' Callback delegates
  6.   Private Delegate Function EnumWindowCallbackDelegate(ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
  7.  
  8.   ' Win32 API's
  9.   Private Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As EnumWindowCallbackDelegate, ByVal lParam As IntPtr) As Boolean
  10.   Private Declare Auto Function GetWindowTextLength Lib "user32" (ByVal hWnd As IntPtr) As Integer
  11.   Private Declare Auto Function GetWindowText Lib "user32" (ByVal hWnd As IntPtr, ByVal lpString As StringBuilder, ByVal nMaxCount As Integer) As Integer
  12.   Private Declare Function IsWindowVisible Lib "user32" (ByVal hWnd As IntPtr) As Boolean
  13.   Private Declare Function GetParent Lib "user32" (ByVal hWnd As IntPtr) As IntPtr
  14.  
  15.   ' For later use...
  16.   ' Holds the last open excel handle
  17.   Dim excelPtr As IntPtr = IntPtr.Zero
  18.  
  19.   Public Function GetLastActiveExcelHandle() As IntPtr
  20.  
  21.     Dim enumProcDelegate As New EnumWindowCallbackDelegate(AddressOf EnumWindowsCallbackFunction)
  22.     EnumWindows(enumProcDelegate, excelPtr)
  23.     If (excelPtr <> IntPtr.Zero) Then
  24.       MsgBox("Some excel files are opened")
  25.     Else : MsgBox("No excel file is opened")
  26.     End If
  27.     Return excelPtr
  28.  
  29.   End Function
  30.  
  31.   Private Function EnumWindowsCallbackFunction(ByVal hWnd As IntPtr, ByVal lParam As IntPtr) As Boolean
  32.  
  33.     If IsWindowVisible(hWnd) And GetParent(hWnd) = IntPtr.Zero Then
  34.       Dim buffer As StringBuilder = New StringBuilder(GetWindowTextLength(hWnd) + 1)
  35.       GetWindowText(hWnd, buffer, buffer.Capacity)
  36.       Dim windowText As String = buffer.ToString()
  37.       If windowText.Contains("Microsoft Excel") Then
  38.         MsgBox(windowText)
  39.         excelPtr = hWnd
  40.         Return False
  41.       End If
  42.     End If
  43.     Return True
  44.  
  45.   End Function
  46.  
  47. End Class

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

Expand|Select|Wrap|Line Numbers
  1.   Sub ExcelPaste()
  2.  
  3.     ' Gets cell info in the paste range for undo, then pastes textbox data
  4.  
  5.     Dim FirstCrLf As UShort = 0
  6.     Dim TextboxText As String
  7.     Dim I As UShort
  8.     Dim NumCols As UInteger = 1
  9.     Dim NumRows As UInteger = 0
  10.     Dim strReader As New StringReader(TSForm.tboxResults.Text)
  11.     Dim RowText As String = ""
  12.     Dim ThisCell As String
  13.  
  14.     ' Get the latest excel handle. 
  15.     Dim ret As IntPtr = myref.GetLastActiveExcelHandle()
  16.     Dim myExcelProcesses As Process() = Process.GetProcessesByName("Excel")
  17.     For Each xlProcess As Process In myExcelProcesses
  18.       If (ret = xlProcess.MainWindowHandle) Then
  19.  
  20.         ' Determine how much text to paste
  21.         If TSForm.tboxResults.SelectionLength = 0 Then
  22.           TextboxText = TSForm.tboxResults.Text
  23.           Clipboard.SetDataObject(TSForm.tboxResults.Text)
  24.         Else
  25.           TextboxText = TSForm.tboxResults.SelectedText
  26.           Clipboard.SetDataObject(TSForm.tboxResults.SelectedText)
  27.         End If
  28.  
  29.         ' Find end of first line
  30.         FirstCrLf = InStr(TextboxText, Chr(13))
  31.         ' Count number of tab characters in first line = number of columns
  32.         For I = 1 To FirstCrLf
  33.           If Mid(TextboxText, I, 1) = Chr(9) Then NumCols += 1
  34.         Next
  35.  
  36.         ' Count number of rows
  37.         Do
  38.           RowText = strReader.ReadLine()
  39.           If RowText Is Nothing Then Exit Do
  40.           NumRows += 1
  41.         Loop Until (RowText Is Nothing)
  42.         strReader.Close()
  43.  
  44.         ' Get existing cell data (for undo), then paste textbox data
  45.         With xlApp
  46.           .Windows(xlFileName).Activate()
  47.           ThisCell = .ActiveCell.Address
  48.           ' Get current data for undo
  49.           PasteRange = .Range(ThisCell, .Cells(.ActiveCell.Row + NumRows - 1, .ActiveCell.Column + NumCols - 1))
  50.           ReDim CellValues(NumRows - 1, NumCols - 1)
  51.           CellValues = PasteRange.Formula
  52.           ' Paste data
  53.           .ActiveSheet.PasteSpecial(Format:="Text")
  54.         End With
  55.         If myExcelProcesses.Length = 0 Then             ' If Excel isn't found  
  56.           Dim xL As Process = Process.Start("excel")    ' Open Excel  
  57.           'Sleep(500)                                   ' Wait for Excel to start before continuing
  58.         Else
  59.           AppActivate(xlProcess.Id)
  60.         End If
  61.       End If
  62.     Next xlProcess
  63.  
  64.     TSForm.btnExcelUndo.Enabled = True
  65.  
  66.   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!
Jan 12 '09 #24

rauty
P: 16
Hi Ramk,

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

Expand|Select|Wrap|Line Numbers
  1. Dim myref As Class1 = New Class1
  2.  
  3.   Sub ExcelPaste()
  4.  
  5.     ' Gets cell info in the paste range for undo, then pastes textbox data
  6.  
  7.     Dim FirstCrLf As UShort = 0
  8.     Dim TextboxText As String
  9.     Dim I As UShort
  10.     Dim NumCols As UInteger = 1
  11.     Dim NumRows As UInteger = 0
  12.     Dim strReader As New StringReader(TSForm.tboxResults.Text)
  13.     Dim RowText As String = ""
  14.     Dim ThisCell As String
  15.  
  16.     ' Get the latest excel handle. 
  17.     Dim ret As IntPtr = myref.GetLastActiveExcelHandle()
  18.     Dim myExcelProcesses As Process() = Process.GetProcessesByName("Excel")
  19.     For Each xlProcess As Process In myExcelProcesses
  20.       If (ret = xlProcess.MainWindowHandle) Then
  21.  
  22.         ' Determine how much text to paste
  23.         If TSForm.tboxResults.SelectionLength = 0 Then
  24.           TextboxText = TSForm.tboxResults.Text
  25.           Clipboard.SetDataObject(TSForm.tboxResults.Text)
  26.         Else
  27.           TextboxText = TSForm.tboxResults.SelectedText
  28.           Clipboard.SetDataObject(TSForm.tboxResults.SelectedText)
  29.         End If
  30.  
  31.         ' Find end of first line
  32.         FirstCrLf = InStr(TextboxText, Chr(13))
  33.         ' Count number of tab characters in first line = number of columns
  34.         For I = 1 To FirstCrLf
  35.           If Mid(TextboxText, I, 1) = Chr(9) Then NumCols += 1
  36.         Next
  37.  
  38.         ' Count number of rows
  39.         Do
  40.           RowText = strReader.ReadLine()
  41.           If RowText Is Nothing Then Exit Do
  42.           NumRows += 1
  43.         Loop Until (RowText Is Nothing)
  44.         strReader.Close()
  45.  
  46.         ' Get existing cell data (for undo), then paste textbox data
  47.         With xlApp
  48.           .Windows(xlFileName).Activate()
  49.           ThisCell = .ActiveCell.Address
  50.           ' Get current data for undo
  51.           PasteRange = .Range(ThisCell, .Cells(.ActiveCell.Row + NumRows - 1, .ActiveCell.Column + NumCols - 1))
  52.           ReDim CellValues(NumRows - 1, NumCols - 1)
  53.           CellValues = PasteRange.Formula
  54.           ' Paste data
  55.           .ActiveSheet.PasteSpecial(Format:="Text")
  56.         End With
  57.         If myExcelProcesses.Length = 0 Then             ' If Excel isn't found  
  58.           Dim xL As Process = Process.Start("excel")    ' Open Excel  
  59.           'Sleep(500)                                   ' Wait for Excel to start before continuing
  60.         Else
  61.           AppActivate(xlProcess.Id)
  62.         End If
  63.       End If
  64.     Next xlProcess
  65.  
  66.     TSForm.btnExcelUndo.Enabled = True
  67.  
  68.   End Sub
Jan 12 '09 #25

Post your reply

Sign in to post your reply or Sign up for a free account.