Link to home
Start Free TrialLog in
Avatar of PeterFrb
PeterFrbFlag for United States of America

asked on

VBA vs. VB.Net: .Net not returning Excel Objects

I have attached two files to this post, which must be open and run to really understand what I'm trying to do.  The idea of having a function that can return all instances of Excel applications running on the desktop is extremely valuable and compelling to me, and I have designed just such a process as both a VBA and VB.Net application.  [A huge shout-out goes to "ForEachLoop" (yes, his online name) at the site http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel, who planted the seed on which I built my own app.]  

The VBA piece inside my Access application performs as I designed it: return an array of every Excel application running on the desktop, along with its handle.  However, the VB.Net code fails to accomplish the feat.  When I run the routine "ReturnExcelObjectFromHWnd", at the point that the routine calls "IIDFromString", I contrast the results of the IID variable: in VBA, the "IIDFromString" has changed the variable contents in meaningful ways.  In VB.Net, the contents are unchanged.  

I made a notation in the code of where the breakdown between VBA and VB.Net appears, and I've included an image of that portion of code.  

I've worked really hard to distill all the problem into its relevant components, and hopefully you can replicate my findings and come up with a solution.

Thanks, ~Peter Ferber

P.S.  I've added the file handle because that's the environment I've inherited.  My main interest is in the Excel Application objects.   If VB.Net has its own method of achieving the same result without the use of DOS declaritives, I am just fine dispensing the file handle!  The real gold here is the application objects.

Late-breaking news: I discovered that my strPtr function in VB.Net calls varptr, but the results are different.  In VBA, there is a call to strPtr, and I can't find a reference anywhere to that function.  The calls in the different languages yield different values, which is likely the cause of the difficulty.  I'm still looking for a means to solve the problem, and this may be the tip I need.


User generated imageClean-FindExcel.accdb
modProcess.vb
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

What version of Excel are you working with?  I have a routine that enumerates the objects registered in the Running Objects Table (ROT), and Excel 2010 causes a problem, since it doesn't register with the ROT as expected.
ASKER CERTIFIED SOLUTION
Avatar of Corey Scheich
Corey Scheich
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterFrb

ASKER

This definitely contains the foundation for what I'm trying to do.  I obviously need to brush up on processes, but this gives me code to really peruse and discern what's happening and adapt it to my own uses.  Thank you.
Just to confirm, a very slight tweaking of this code rendered spot-on results.  If I knew how simple it would actually be, I would have graded this as excellent, not just good.  Consider it done.
If you want to change the grade you could use the Request attention button.  Either way I am glad it accomplished what you needed.