vba 将值从 VB.NET 传递给 excel 输入框

Passing value to excel inputbox from VB.NET

提问by Shouvik

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.

我正在尝试在一些具有宏的 Excel 工作表上自动填充数据。现在excel受到保护，我无法获得密钥。现在我可以运行宏了，但是当我尝试传递参数时，我发现参数不匹配。

If I just run the macro with the name, I get an inputboxwhich 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 inputboxas 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.

如果我只运行带有名称的宏，我会得到一个inputbox将额外参数作为输入并自动生成列的一些值的 an。我现在必须手动输入这个值inputbox。有什么方法可以使该过程自动化，即捕获宏在 vb.net 脚本中抛出的输入框并从那里输入值？即，我想运行宏并在弹出窗口要求我输入一些值后，使用 vb.net 代码将值输入到该弹出窗口。

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.

这也使我的 vb.net 脚本无法移动到下一行。

回答by Siddharth Rout

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

就像你和我，我们俩有名字，类似的窗口有handles(hWnd)，Class等一旦你知道那是什么hWnd，它是更容易与窗口交互。

This is the screenshot of the InputBox

这是输入框的截图

Logic:

逻辑：

1. Find the Handle of the InputBox using FindWindowand 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 SendMessageto write to it.

1. 找到FindWindow输入框使用的句柄和输入框的标题是Create Network IDs

2. 找到后，使用以下命令在该窗口中找到编辑框的句柄 FindWindowEx

3. 找到编辑框的句柄后，只需使用SendMessage向其写入即可。

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

在下面的示例中，我们将写入It is possible to Interact with InputBox from VB.NetExcel 输入框。

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 InputBoxfunction two ways in Excel

您可以InputBox在 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 OKand CANCELbuttons 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 OKand CANCELbuttons easily :)

在您的情况下，使用了第一种方式，不幸的是，OK和CANCEL按钮没有句柄，因此很遗憾，您必须使用SendKeys (Ouch!!!)它来与之交互。如果您通过第二种方法生成了 Inbutbox，那么我们可以轻松地自动化OK和CANCEL按钮:)

Additional Info:

附加信息：

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

在 Visual Studio 2010 Ultimate（64 位）/ Excel 2010（32 位）上测试

Inspired by your question, I actually wrote a blog Articleon how to interact with the OKbutton on InputBox.

受你的问题启发，我实际上写了一篇关于如何与InputBox上的按钮进行交互的博客文章OK。

回答by Shouvik

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.

目前，我采用了一种方法，在脚本调用宏之前运行线程。线程检查输入框是否已被调用。如果是，它会从该位置获取值并使用 sendkeys 提交该框。

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