My Blog

Excel Inputbox Automation from VB.NET

While answering a recent question in stackoverflow, I came across this question.

These are the conditions that I used during testing

  • VB.Net 2010 Ultimate 64 bit / Win 7 64 Bit / Excel 2010 32 Bit
  • Inputbox was launched in Excel using InputBox (and not Application.InputBox)

Sample Code to Launch the Excel InputBox

Sub Sample()
    Dim Ret
    Ret = InputBox("This is an example of inputbox", "Sample Title", "Sample Title")
End Sub

Logic that I followed for both tests

  • Find the Handle of the InputBox using FindWindow and the caption of the Input Box which is Sample Title
  • Once that is found, find the handle of the Edit Box in that window using FindWindowEx
  • Once the handle of the Edit Box is found, simply use SendMessage to write to it.
  • Find the handle of the OK Button in that window using FindWindowEx and Click OK using SendMessage. Note there are 3 buttons and hence we need to find the right button by checking it’s caption.

In the below example we would be writing It is possible to Interact with InputBox from VB.Net to the Excel Inputbox.

Create a Form in your VB.Net Form and add a button to it.

TEST 1 (Writing To InputBox)

Use this code

Imports System.Runtime.InteropServices
Imports System.Text

Public Class Form1
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Integer

    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Integer, ByVal hWnd2 As Integer, ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Integer

    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
   (ByVal hwnd As Integer, ByVal wMsg As Integer, ByVal wParam As Integer, _
    ByVal lParam As String) As Integer

    Const WM_SETTEXT = &HC

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Ret As Integer, ChildRet As Integer

        '~~> String we want to write to Input Box
        Dim sMsg As String = "It is possible to Interact with InputBox from VB.Net"

        '~~> Get the handle of the InputBox Window
        Ret = FindWindow(vbNullString, "Sample Title")

        If Ret <> 0 Then
            MessageBox.Show("Input Box Window Found")

            '~~> Get the handle of the Text Area "Window"
            ChildRet = FindWindowEx(Ret, 0, "EDIT", vbNullString)

            '~~> Check if we found it or not
            If ChildRet <> 0 Then
                MessageBox.Show("Text Area Window Found")
                '~~> Send the Text
                SendMess(sMsg, ChildRet)
            End If
        End If
    End Sub

    Sub SendMess(ByVal Message As String, ByVal hwnd As Long)
        Call SendMessage(hwnd, WM_SETTEXT, False, Message)
    End Sub
End Class

When you run the code this is what you get

TEST 2 (Writing To InputBox and clicking the OK Button)

Now let’s try and click the `OK` Button.

Before you do that, change your Excel Macro which calls the InputBox to this so that we can see what happens when the `OK` button is clicked.

Sub Sample()
    Dim Ret
    Ret = InputBox("This is an example of inputbox", "Sample Title")
    If Not Ret = "" Then MsgBox "You Typed " & Ret & " and clicked `OK`"
End Sub

Next Use this code from VB.Net

Imports System.Runtime.InteropServices
Imports System.Text

Public Class Form1
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Integer

    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
    (ByVal hWnd1 As Integer, ByVal hWnd2 As Integer, ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Integer

    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
   (ByVal hwnd As Integer, ByVal wMsg As Integer, ByVal wParam As Integer, _
    ByVal lParam As String) As Integer

    Private Declare Function GetWindowTextLength Lib "user32" Alias _
    "GetWindowTextLengthA" (ByVal hwnd As Integer) As Integer

    Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Integer, ByVal lpString As String, ByVal cch As Integer) As Integer

    Const WM_SETTEXT = &HC
    Const BM_CLICK As Integer = &HF5

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Ret As Integer, ChildRet As Integer, OKRet As Integer
        Dim strBuff As String, ButCap As String

        '~~> String we want to write to Input Box
        Dim sMsg As String = "It is possible to Interact with InputBox from VB.Net"

        '~~> Get the handle of the "File Download" Window
        Ret = FindWindow(vbNullString, "Sample Title")

        If Ret <> 0 Then
            MessageBox.Show("Input Box Window Found")

            '~~> Get the handle of the Text Area "Window"
            ChildRet = FindWindowEx(Ret, 0, "EDIT", vbNullString)

            '~~> Check if we found it or not
            If ChildRet <> 0 Then
                MessageBox.Show("Text Area Window Found")
                '~~> Send the Text
                SendMess(sMsg, ChildRet)


                '~~> Get the handle of the Button (There are 3)
                ChildRet = FindWindowEx(Ret, 0, "Button", vbNullString)

                '~~> Check if we found it or not
                If ChildRet <> 0 Then
                    MessageBox.Show("Button's Handle Found")

                    '~~> Now we will check the caption of each button to see if it is `OK`
                    '~~> Get the caption of the button
                    strBuff = New String(Chr(0), GetWindowTextLength(ChildRet) + 1)
                    GetWindowText(ChildRet, strBuff, Len(strBuff))
                    ButCap = strBuff

                    '~~> Loop through all child windows
                    Do While ChildRet <> 0
                        '~~> Check if the caption has the word "OK"
                        If InStr(1, ButCap, "OK") Then
                            '~~> If this is the button we are looking for then exit
                            OKRet = ChildRet
                            MessageBox.Show("The Handle of OK Button was found and will now be clicked")
                            Exit Do
                        End If

                        '~~> Get the handle of the next child window
                        ChildRet = FindWindowEx(Ret, ChildRet, "Button", vbNullString)
                        '~~> Get the caption of the child window
                        strBuff = New String(Chr(0), GetWindowTextLength(ChildRet) + 1)
                        GetWindowText(ChildRet, strBuff, Len(strBuff))
                        ButCap = strBuff
                    Loop

                    '~~> Check if we found it or not
                    If OKRet = 0 Then
                        MessageBox.Show("The Handle of OK Button was not found")
                        Exit Sub
                    End If

                    SendMessage(OKRet, BM_CLICK, 0, vbNullString)
                Else
                    MessageBox.Show("The Handle of OK Button was not found")
                End If
            Else
                MessageBox.Show("The handle of Text Area Window was not Found")
            End If
        Else
            MessageBox.Show("The handle of Input Box Window was no Found")
        End If
    End Sub

    Sub SendMess(ByVal Message As String, ByVal hwnd As Long)
        Call SendMessage(hwnd, WM_SETTEXT, False, Message)
    End Sub
End Class

When you run the code you will see this result in Excel

The working becomes easier if you know how to find handles of objects. I use uuSpy/Spy++ to get the details. See this screenshot for the Inputbox.

Hope this helps :)