How to automatically click on open file button running VBA procedure

Question

I am looking for a way to click on a button 'open file'.

I wrote an Excel VBA macro that connects with SAP module. What it should do is to add a file as an attachment in SAP. As a result I get pop-up window, just a standard Windows "open file" window to get the "file name" field and "open file" button.

My question is how to automatically insert a file name (always known) and click on that "open file" button?

I tried sendkeys from within Excel VBA, but it turns out that once the "open file" window pops up the whole Sub routine stops.

I know I need Winapi to do this.

Answer 1

You probably need to do a DoEvents to allow the keys to be processed by SAP.

Quoting from VBA help:

DoEvents Function

Yields execution so that the operating system can process other events.

Syntax

DoEvents( )

Remarks

The DoEvents function returns an Integer representing the number of open forms in stand-alone versions of Visual Basic, such as Visual Basic, Professional Edition. DoEvents returns zero in all other applications.

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.