Kemmuniemans
Board Regular
- Joined
- Feb 25, 2013
- Messages
- 57
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:
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.
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:
Code:
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.