We help IT Professionals succeed at work.
Get Started

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

1,351 Views
Last Modified: 2012-08-15
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.


Image of error messageClean-FindExcel.accdb
modProcess.vb
Comment
Watch Question
Developer
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE