How to re-connect an Excel.Application variable to a disconnected Excel instance?  
Author Message
johnson hk





PostPosted: Visual Basic for Applications (VBA), How to re-connect an Excel.Application variable to a disconnected Excel instance? Top

Hi,

Automating Excel is wonderfull. I program it quite offen and I quite enjoy it.

I have a question:

When a program with new a Excel.Application is broken and stopped, the created Excel instance will be deattached to any VBA variable. In this case, I need to be "End Process" manually in the window task manager. I do this more than 50 times in one day of programming. Without doing this, I once had found more than 20 Excel instances in the task manager. It is terrible.

I tried following, the program broken at J=27 in my 1Gbyte RAM notebook.

Dim J As Byte, mExcel(1 To 50) As Excel.Application
For J = 1 To 50
Debug.Print J;
Set mExcel(J) = New Excel.Application
Next
Stop

My question is how can I scan all current Excel instances to see those created by my VBA (I can put some marker somewhere in Excel instances to check if the Excel instance is created by my VBA). Once such Excel instances are found, I want to re-connect it to a VBA variable as Excel.Application, so that I can quit those Excel instance without manually "End Process" to each of them

Regards,

Johnson Cheung



Microsoft ISV Community Center Forums1  
 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), How to re-connect an Excel.Application variable to a disconnected Excel instance? Top

Hello Johnson.

Woah! No!.... you do not want to be creating 50 instances of Excel. There's no need and your system will just slow right down to a stand still or at least a very slow crawl. Your putting a lot of strain on the system for no reason.

Instead of 50 instances of Excel, create 1 instance and use that for all processing. If you looking to have 50 workbooks open at the same time then open them all in the same instance of Excel. This will still put your system under some strain but controlling Excel will be far easier. You won't need a marker then as all workbooks in the same automated instance of Excel where created by you and you can close them all down by closing one Excel instance. You can also close single workbooks, again only using one Excel instance.



 
 
johnson hk





PostPosted: Visual Basic for Applications (VBA), How to re-connect an Excel.Application variable to a disconnected Excel instance? Top

Hi Derek,

Thank you for your reply. Sorry to tell you that you are not answering my question.

My question is how can I scan all current Excel instances to see those created by my VBA (I can put some marker somewhere in Excel instances to check if the Excel instance is created by my VBA). Once such Excel instances are found, I want to re-connect it to a VBA variable which is declared as Excel.Application, so that I can quit those Excel instance without manually "End Process" to each of them

In short, the question is:

How to re-connect an Excel.Application variable to a disconnected Excel instance

Regards,

Johnson Cheung


 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), How to re-connect an Excel.Application variable to a disconnected Excel instance? Top

Mr Cheung,

I am aware that I did not answer your question. I felt your approach in creating multiple instances of Excel, which it is reasonable to presume leads to the problem of having several unconnected instances, was perhaps not a good approach to take. Changing the multiple instance approach to only a single instance approach would mean that instead of needing to scan all previously created and disconnected instances of Excel you'd only have one instance that would remain connected at all times.

Finding a solution to a problem, in my honest opinion, should be secondary to removing the problem all together.

To answer you question though I presume it would be possible. You will need to dip into the WinAPI as there isn't, as far as I know, an inbuilt Excel function to do it. This article here covers the API calls you'll need. Your idea to tag an instance of Excel I understand but you should do it the other way and in your application hold a reference to the process ID of the instances you create.

http://www.awprofessional.com/articles/article.asp p=366892&seqNum=3&rl=1

Again this isn't a direct answer but hopefully you can piece the code together and do what you need.



 
 
johnson hk





PostPosted: Visual Basic for Applications (VBA), How to re-connect an Excel.Application variable to a disconnected Excel instance? Top

Hi Derek,

Thanks again for your reply.

Scanning through the link you given, the hope in doing re-connect an Excel.Application variable to a disconnected Excel instance is still little, even through you and me believe that it can definely be done. Anyway, your given link provides some hints.

- It can obtain a "window handle" of a disconnected Excel instance by simple VBA programming with calling 2 WIN32API functions: GetCurrentProcessId Lib "kernel32" & GetWindowThreadProcessId Lib "user32". I have tried it, it works.

- Then, once a "window handle" of a discounntected Excel instance is found, how to assign this Excel instance back to a VBA variable, mExcel, which is declared by the statement: Dim mExcel as Excel.Application

- Or in other question, once a "window handle" of a discounntected Excel instance is found, how to call the Application.Quit function by referring this "window handle" of the disconnect Excel instance by some sort of WIn32API

- Or in further change the question, once a "window handle" of a COM Object instance is found, which Win32API should be used to call this COM Object provided function by name (I think this question should put in other forums, as it will out of the scope of this forums - VBA).

Regards,

Johnson Cheung