Automating MS Access VBA procedure - assign string variable to InputBox

Question

I am trying to automate at first glance a very simple task via C# - to pass a string value to an inputBox called through VBA function.

In order to do that I have used Visual C# .NET Automation Client to create an instance of MS Access and open the required database:

using System.Management.Automation;
using Access = Microsoft.Office.Interop.Access;

Access.Application oAccess = new Access.Application();
        oAccess.Visible = true;
        oAccess.OpenCurrentDatabase("D:\\path\\to\\database", false, "");

In the mentioned database we have a Macro that calls a VBA function via the "RunCode" action. This public Function then calls another private Sub:

Public Function ImportMappingDatabases()
  ImportMappings InputBox("Specify Path", "Path", mstrcDefaultPath), _
    InputBox("Choose Package", "Package", "")
End Function

Private Sub ImportMappings(Optional Path As String = mstrcDefaultPath, Optional Package As String = "")

Here is where I got stuck. The input box with the title "Path" wants the user to input a certain path that the later code uses for reasons that do not need to be explained. The same is valid for the input box with the title "Package". I assume there is a way to "pass" a string value from the C# code to the input box entry field, but I do not know how to refer to the input box and set the value. After that is done the user is required to click on the "OK" button or the "Cancel", the same question applies, I will try to synthesize it in 1 sentence:

How to get a reference of that input box, set the input value, and "click" one of the buttons?

More information:

I am calling the public function via: oAccess.Run("ImportMappingDatabases");

I can not modify the VBA functions/subs.

Update 1:

Check my answer below.

I think you'd need to use Win API calls to populate the input boxes. — Tim Williams
Too bad cannot modify VBA Sub. If Sub had an argument, value could be passed to the argument via Run. At least I can in a VBScript so assume C# could as well. oAccess.Run "ImportMappingDatabases", varInput; I don't use macros. — June7
Your not attempting to do object consumption and automation but attempting to emulate keyboard - and that's not really very practical. This is much like trying to screen scrape a web site and enter values - difficult, error prone and not all that reliable. If a public sub (for function) was created in VBA, then you could consume + call + use that VBA sub. But such routines can't prompt the user for keyboard input since then you now having to use keypress(s) to emulate keypresses. You can no more do this then say consume a .net program as a class that wants console keyboard input. — Albert D. Kallal
@TimWilliams Can you provide any resource pointing me to the right direction, please? . — l000p
@AlbertD.Kallal In this particular case we get the "keyboard strokes" in the form of automatically generated array of strings that later can be used for input, which is only a part of the steps done previously all manually and was as you guessed it - very error prone. The attempt here is to use what we have as legacy code - there is still no permission from the software owner to touch the VBA. — l000p

l000p l000p · Accepted Answer · 2021-01-27T10:32:21

I have found a way to get a handle to the inputBox and a way to pass the string value to it. You need the following imports:

[DllImport("User32.dll", CharSet = CharSet.Auto)] static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
[DllImport("User32.dll", CharSet = CharSet.Auto)] static extern IntPtr FindWindowEx(IntPtr parentHandle, IntPtr childAfter,
                                                                           string lpcClassName, string lpcWindowTitle);
[DllImport("User32.dll", CharSet = CharSet.Auto)] static extern UInt32 SendMessage(IntPtr handle, UInt32 message,
                                                                                           IntPtr wParam, string lParam);

Then you can use Win API calls to find your MS Access window and the inputBox window handles:

IntPtr parent = FindWindow(null, "The access Window title here");
IntPtr childInputBox = IntPtr.Zero;
IntPtr TextBox = IntPtr.Zero;
if (parent != IntPtr.Zero)
{
   childInputBox = FindWindowEx(parent, IntPtr.Zero, string.Empty, "InputBox name here");
  if (childInputBox != IntPtr.Zero)
  {
    TextBox = FindWindowEx(childInputBox, IntPtr.Zero, "Edit", 
    string.Empty);
    if (TextBox != IntPtr.Zero)
    {
      SendMessage(TextBox, WM_SETTEXT, new IntPtr(0), "Your out string to the input here");
    }
  }
}

Here I hit a wall, because I actually have to call my macro before I try to capture the handle. So calling the macro with:

oAccess.Run("TheNameOfTheMacro");

Will run it on the same thread, therefore rendering our Window forms application unusable until the macro is done. This complication led me to get a permission to extend the VBA code with a public function accepting string as a parameter (as discussed in the comments) and continued from there. If you how ever decide that you have to use the Input Box you have to run the macro on different thread and somehow check if the Input Box is "summoned" if so - send the message.

Automating MS Access VBA procedure - assign string variable to InputBox

1 Answers