Press Button on a pop-up form of an add-in

[Kemmuniemans]

I am trying to automate pressing a button on a userform generated by an add-in.

The add-in is from an ERP-program and makes it possible to extract data from this ERP system by preset functions or by giving in your own SQL-code.
I use it for this last option by extracting data using my own SQL-query.

I can manually connect to the database with use of a connectionstring, in most cases this works, but I stumbled upon a problem where my SQL-code uses SUM or average functions.
When this is the case it errors, so this is not really an option.

I got it to work the following way:

    Application.OnTime Now + TimeValue("00:00:01"), "OKUitvoeren"
    Set CtlMenuItem = CommandBars(Hydrabar).Controls(4)
    CtlMenuItem.Execute
End Sub

Sub OKUitvoeren()
Application.SendKeys ("%o")
End Sub

I have to use the on-time, because when I use the "CtlMenuItem.Execute" my code stops because the userform shows. I worked around this byy using the application.OnTime command to press ALT + o 1 second later. This is not ideal, but it works and I really see no other way to do this. On top of that, when using the SendKeys, the NumLock toggles. I could work around this by catching the state of numlock before the code and set it back to that state after, but I was actually hoping not having to use the sendkeys command anymore because it seems kinda bugged and I actually have no idea if I can trust it.

I could allow references to the add-in, and manually call the function under pressing the ok button, but I don't want that either as the add-in is rather big and I have no idea wether their might be duplicate names when I do that, which cause an even bigger hassle. When I do this, I have to activate the reference on ever PC/laptop within the company, which is also a bit of a hassle.

I am basically here to see if anyone has a better idea for this, aside from what I am currently working with, or what in their opinions would be the best way to go at it if no better solutions arise.
Hope to get a bit of help with this, as it as been mind-boggling so far.

 

[sijpie]

I do not think there is a better way to do this. Calling the button press in the add-in will very likely fail, because usually the CmndButton_Click() event in the form module is set to Private: ie you cannot access it from outside the form. But you can check if it is available, by looking at which functions are visible for the form.

Sendkeys is not buggy, but the implementation of sendkeys is difficult as there are other windows processes running and so you have timing issues.

 

[Kemmuniemans]

Yeah, the CmndButton_Click() event is private and I was thinking there is no better way to do it than my current way using sendkeys.

The SendKeys command works aside from the numlock toggle. But I could just catch its state and set it back I suppose.
Aside from that, do you have any idea if it's possible to use complex sql-queries within vba? or why it is erroring on me? If you need an example for it to be certain, let me know and I'll provide one.

Thanks for your comment and thanks in giving me the confirmation of something I already assumed.

 

[sijpie]

You can do complex SQL queries in VBA. But start a new thread on that subject, else you will not get the best answers