Passing value to excel inputbox from VB.NET

excel, excel-automation, vb.net, vba

Solution

Like you and me, we both have names, similarly windows have `handles(hWnd)`, `Class` etc. Once you know what that `hWnd` is, it is easier to interact with that window.

This is the screenshot of the InputBox

Logic:

Find the Handle of the InputBox using `FindWindow` and the caption of the Input Box which is `Create Network IDs`

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.

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

Code:

Create a Form and add a button to it.

Paste 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 "Input Box" Window
        Ret = FindWindow(vbNullString, "Create Network IDs")

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

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

            '~~> Check if we found it or not
            If ChildRet <> 0 Then
                'MessageBox.Show("Text Area Window Found")
                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

ScreenShot

When you run the code this is what you get

EDIT (Based on further request of automating the OK/Cancel in Chat)

AUTOMATING THE OK/CANCEL BUTTONS OF INPUTBOX

Ok here is an interesting fact.

You can call the `InputBox` function two ways in Excel

Sub Sample1()
    Dim Ret
    Ret = Application.InputBox("Called Via Application.InputBox", "Sample Title")
End Sub

and

Sub Sample2()
    Dim Ret
    Ret = InputBox("Called Via InputBox", "Sample Title")
End Sub

In your case the first way is used and unfortunately, The `OK` and `CANCEL` buttons do not have a handle so unfortunately, you will have to use `SendKeys (Ouch!!!)` to interact with it. Had you Inbutbox been generated via the second method then we could have automated the `OK` and `CANCEL` buttons easily :)

Additional Info:

Tested on Visual Studio 2010 Ultimate (64 bit) / Excel 2010 (32 bit)

Inspired by your question, I actually wrote a blog Article on how to interact with the `OK` button on InputBox.

Problem

I am trying to automate data population on some excel sheets that have some macros. Now the excel is protected and I cannot get the secret key. Now I am able to run the macros but when I try to pass arguments I get arguments mismatch. If I just run the macro with the name, I get an `inputbox` which takes an extra argument as input and auto generates some of the values for the columns. I have to manually enter this value into the `inputbox` as of now. Is there any way that I could automate that process, i.e capture the inputbox thrown by the macro in the vb.net script and enter the values from there? i.e., I would like to run the macro and after I get the popup asking me to enter some value, use the vb.net code to enter the value to that popup. Here is what I have till now ``` Public Class Form1 Dim excelApp As New Excel.Application Dim excelWorkbook As Excel.Workbook Dim excelWorkSheet As Excel.Worksheet Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click excelWorkbook = excelApp.Workbooks.Open("D:/excelSheets/plan_management_data_templates_network.xls") excelApp.Visible = True excelWorkSheet = excelWorkbook.Sheets("Networks") With excelWorkSheet .Range("B7").Value = "AR" End With excelApp.Run("createNetworks") // now here I would like to enter the value into the createNetworks Popup box excelApp.Quit() releaseObject(excelApp) releaseObject(excelWorkbook) End Sub ``` Macro definition ``` createNetworks() //does so basic comparisons on existing populated fields //if true prompts an inputbox and waits for user input. ``` This stall my vb.net script too from moving to the next line.

Original source