Passing value to excel inputbox from VB.NET

问题:

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.

回答1:

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.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.

回答2:

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