传递值从VB.NET擅长输入框(Passing value to excel inputbox fr

我想自动数据填充一些Excel中有一些宏表。现在，Excel是受保护的，我不能获得密钥。现在，我能够运行宏，但是当我试图通过争论，我得到的参数不匹配。

如果我只是运行一个名为宏，我得到一个inputbox这需要一个额外的参数作为输入，并自动生成一些对列中的值的。我必须手动输入该数值到inputbox ，截至目前。有没有办法，我可以自动执行过程中的任何方式，即捕捉宏在vb.net脚本抛出输入框，并从那里输入值是多少？即，我想运行宏后，我得到了弹出要我输入一些值，使用vb.net代码输入数值到该弹出窗口。

下面是我到现在为止

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

宏定义

createNetworks()
//does so basic comparisons on existing populated fields
//if true prompts an inputbox and waits for user input.

这从移动到下一行搪塞我vb.net脚本了。

Answer 1:

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

这是的InputBox截图

逻辑：

查找使用的InputBox的手柄FindWindow和输入框的标题是Create Network IDs
一旦被发现，找到编辑框的手柄在使用该窗口FindWindowEx
一旦编辑框的手柄被发现，简单地使用SendMessage来写它。

在下面的例子中，我们将书写It is possible to Interact with InputBox from VB.Net对Excel输入框。

代码：

创建窗体并添加一个按钮即可。

粘贴此代码

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

截图

当您运行的代码，这是你得到了什么

编辑（在聊天基于自动确定的进一步请求/取消）

自动化确定/取消输入框制钮扣

确定这里是一个有趣的事实。

你可以调用InputBox函数在Excel两种方式

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

和

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

在你的情况下，使用第一种方式和遗憾的是， OK和CANCEL按钮，没有手柄，使不幸的是，你将不得不使用SendKeys (Ouch!!!)与它进行交互。假如你Inbutbox已通过第二种方法生成的，然后我们可以自动的OK和CANCEL容易按钮:)

其他信息 ：

测试上的Visual Studio 2010极限（64位）/ Excel 2010中（32位）

通过你的问题的启发，我居然写了一篇博客文章上如何与互动OK InputBox上按钮。

Answer 2:

目前，我使用，我运行一个线程之前将宏脚本调用的方法。如果输入框被称为线程检查。如果是，它从该位置拿起的价值和使用的SendKeys，提交框。

这是一个基本的解决方案，但我希望有更优雅的解决这个问题。

我的解决办法代码：

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

文章来源: Passing value to excel inputbox from VB.NET