从VB.NET传递给excel输入框的值 [英] Passing value to excel inputbox from VB.NET

问题描述

我正在尝试自动化一些具有一些宏的excel表的数据。现在，excel是受保护的，我不能得到密钥。现在我可以运行宏，但是当我尝试传递参数时，我得到的参数不匹配。

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

这是我到现在为止

 公共类Form1 
 Dim excelApp作为新的Excel.Application 
 Dim excelWorkbook作为Excel.Workbook 
 Dim excelWorkSheet作为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）
 
使用excelWorkSheet 
 .Range（B7）。值=AR
结束
 
 excelApp.Run（createNetworks）
 / /现在在这里我想输入的值到createNetworks弹出框
 excelApp.Quit（）
 releaseObject（excelApp）
 releaseObject（excelWorkbook）
 End Sub 
  

宏定义

  createNetworks（）
 //对现有填充字段进行基本比较
 //如果真的提示输入框，并等待用户输入。 
  

这个摊位我的vb.net脚本也没有移动到下一行。

解决方案

像你和我一样，我们都有名字，windows也有句柄（hWnd） Class 等。一旦你知道 hWnd 是什么，那么这个窗口更容易互动。

这是InputBox的屏幕截图

逻辑：

1. 使用 FindWindow 找到InputBox的句柄，输入框的标题为创建网络ID




2. 找到该窗口后，使用 FindWindowEx




3. 一旦找到编辑框的句柄，只需使用 SendMessage 要写信给你。

在下面的e例如，我们将会写可以将VB.Net 中的InputBox与Excel输入框进行交互。

代码：

创建表单并添加一个按钮。

粘贴此代码

 导入System.Runtime.InteropServices 
导入System.Text 
 
公共类Form1 
私人声明函数FindWindow Libuser32别名FindWindowA_ 
（ByVal lpClassName As String，ByVal lpWindowName As String）As Integer 
 
私有声明函数FindWindowEx Libuser32别名FindWindowExA_ 
（ByVal hWnd1 As Integer，ByVal hWnd2 As Integer，ByVal lpsz1 As String，_ 
 ByVal lpsz2 As String）As Integer 
 
私有声明函数SendMessage Libuser32别名 SendMessageA_ 
（ByVal hwnd As Integer，ByVal wMsg As Integer，ByVal wPara m 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 
 
'~~>我们要写入输入框的字符串
 Dim sMsg As String =可以从VB.Net与InputBox进行交互
 
'~~>获取输入框窗口的句柄
 Ret = FindWindow（vbNullString，创建网络ID）
 
如果Ret<> 0然后
'MessageBox.Show（输入框窗口找到）
 
'~~>获取文本区Window的句柄
 ChildRet = FindWindowEx（Ret，0，EDTBX，vbNullString）
 
'~~>检查我们是否找到
如果ChildRet<> 0然后
'MessageBox.Show（找到文本区域窗口）
 SendMess（sMsg，ChildRet）
如果
结束If 
 End Sub 
 
 Sub SendMess（ByVal Message As String，ByVal hwnd As Long）
调用SendMessage（hwnd，WM_SETTEXT，False，Message）
 End Sub 
结束类
  

ScreenShot

当您运行代码这是你得到的

---

编辑（根据进一步要求自动确定/取消聊天） / p>

自动确定/取消输入按钮

这里是一个有趣的事实

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

  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 Ultimate（64位）/ Excel 2010（32位）

受您的问题的启发，我实际上写了一个博客 Article 如何与InputBox上的 OK 按钮进行交互。 strong>

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.

解决方案

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.

这篇关于从VB.NET传递给excel输入框的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！