Passing value to excel inputbox from VB.NET

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:

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

2. Once that is found, find the handle of the Edit Box in that window using FindWindowEx

3. 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.InteropServicesImports System.TextPublic 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 SubEnd 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.

Currently, I employ a method where I run a thread before the macro is called by the script. The thread checks if the inputbox has been called. If it is, it picks up the value from the location and using sendkeys, submits the box.

This is a rudimentary solution but I was hoping for a more elegant solution to this problem.

My solution Code:

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/some_excel.xls")        excelApp.Visible = True        excelWorkSheet = excelWorkbook.Sheets("SheetName")        With excelWorkSheet            .Range("B7").Value = "Value"        End With        Dim trd = New Thread(Sub() Me.SendInputs("ValueForInputBox"))        trd.IsBackground = True        trd.Start()        excelApp.Run("macroName")        trd.Join()        releaseObject(trd)        excelApp.Quit()        releaseObject(excelApp)        releaseObject(excelWorkbook)    End Sub    Private Sub releaseObject(ByVal obj As Object)       Try           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)           obj = Nothing       Catch ex As Exception           obj = Nothing       Finally           GC.Collect()       End Try    End Sub    Private Sub SendInputs(ByVal noOfIds As String)        Thread.Sleep(100)        SendKeys.SendWait(noOfIds)        SendKeys.SendWait("{ENTER}")        SendKeys.SendWait("{ENTER}")    End Sub