Pass string to VBA application - IPC

[Rudolfmdlt]

Hi All,

I am a complete VBA novice in need of advise and direction. I am an electrical and telecoms engineer and have some skill in Delphi and C++. I need to pass a string between a Windows based app to a VBA app sitting in excel/access in order to integrate an old CRM system with a new telecoms system. :doh:I have done Inter Process Communications (IPC) using the windows Send and Post Message API functions as well as Named Pipes, but always in a backend, data crunching scenario on compiled applications.

I need advice as to what works (best) for IPC with VBA? Can I take VB code and use it in a VBA application to receive a sent message without locking up the whole form? I am completely unclear as to how VBA would deal with interrupts and events while running within Excel or Access.

There is a very basic article describing the process for VB, would this be possible for VBA - support.microsoft.com/kb/176058 ?

Thanks for any direction or advice - I really appreciate your time!

Regards,

Rudolf

PS: If you're really interested, this is what I am trying to accomplish:
1) php site loads and executes hyperlink of form "testprotocol://UniqueCustomerID" #testprotocol is registered in the windows registry to launch my application: autohotkey.com/board/topic/71831-application-url-launch-local-application-from-browser/, this works like a charm, tested for putty, skype and itunes,.
2) Now, this Application needs to accept UniqueCustomerID and send it to MSAccess and MSExcel
3) MSExcel needs to use this ID to query a database and do some calculations based upon additional data using VBA.

I've cracked 1, and I have inherited 3, but I am not sure how to send the data to the VBA application.:thumb

[Kenneth Hobs]

I believe that everything in that article can be done in VBA. The main problem in converting it is that VBA's Userform has no easy way to get its handle as the Form does in vb. I have used API calls to get the Userform handle. I fear that your need is so unique that you may have problems getting help. For the Application, to accept data, I don't know the application so I don't know what it can do. Obviously, using VBA one can send data to applications like Excel and Access.

You may need to move up to vb.net as it has many features that may help.

[Rudolfmdlt]

Morning Kenneth,

Thank you for your reply. Sorry my reply is so late - during this time of year things get a little crazy.

I agree that actually getting the window handle of the VBA application to send the information to it from the VB/C++/C# application is the one major challenge.

I am currently using the clipboard for IPC - which is both a nasty hack but efficient temporary plug. I have seen many developers raked over the coals on forums for this approach! :)

So before I implement another hack, what are your thoughts on:
i) As the VBA application starts up it gets its own windows handle - Me.Hwnd ?
ii) It writes this window handle to the Windows Registry - thread ID on this forum: 16704-VBA-Write-to-the-registry
iii) The VBA application now sits and waits for the receive message event.

On the other application (Visual C++/C#/VB),
i) Reads the registry entry
ii) Sends message
iii) Closes.

I know the registry shouldn't be used as a swap area, but read/writes a couple of times per hour should be fine?

Thanks for taking the time to reply to such a unique issue - I really appreciate your time and expertise! I'm sure getting information from external applications into a VBA application in real time will be useful to other's here as well?

Thanks again for any help!

Rudolf

[SamT]

See if DDE will work for you: Google "pass information to VBA with dde"

[Rudolfmdlt]

Hi Sam, Kenneth,

I have started a new thread under the Access Forums regarding DDE - Direct Data Exchange (DDE) Call Access Macro (http://www.vbaexpress.com/forum/showthread.php?48552-Direct-Data-Exchange-%28DDE%29-Call-Access-Macro)
.

Thanks for pointing out DDE, am now perusing it as I have had major issues creating message hooks in Access (They just end up spinning my CPU up to MAX and maxing out my RAM before the screen going black...:rofl:).

I have had some success with DDE in C++, but I can't get it to work with Access.

Thanks for the help guys! I really appreciate it!

Regards,

Rudolf

[Rudolfmdlt]

Hi Guys,

Thanks for all the help and the pointers. I really appreciate your time.

Just wanted to post back with my solution. I,



Created a macro in my Access frontend that reads from a predefined text file.
Wrote an application in C++ that uses DDE to talk to Access.

You cannot pass parameters to Access in macro calls. You can only call Macros. (You can pass parameters to Word and Excel)
So the C++ application accepts 1 parameter on the command line, and writes that parameter to a file.
The c++ application then calls a predefined Macro in Access that reads from the file.
C++ application exits.


Registered a protocol in the Windows registry, IntegrationProtocolV1 and pointed that protocol to my C++ executable.


So now, when I click my integration button in php "Open In Access", php opens a new link "IntegrationProtocolV1:UserID&UserName&UserSurname". Internet Explorer doesn't open a new tab, but instead launches my application which parses "UserID&UserName&UserSurname" to a text file, closes the file, tells access to read the file and exits.

The time from button click to my Access form updating is <1 s. :)

The reading and writing from/to a text file is a slight hack for IPC, but I couldn't find anything better.

Anyway, it works and works reliably.

Regards,

Rudolf

PS: Any help on improvements will be appreciated. :)