VBA 自动化 - 预填充组合框 [英] VBA Automation - Prefilling Combodate Box

查看：28
本文介绍了VBA 自动化 - 预填充组合框的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试通过 VBA 将 Excel 中的数据预填充到本地 Intranet 网站.

我已经能够将大部分数据预填充到网站，但我在使用这个组合框时遇到了困难:

请查看 HTML 代码:

<label class="col-form-label col-md-3" for="starttime">开始时间</label><div class="col-md-9"><input type="text" id="starttime" name="starttime" class="form-control" size="30" style="display: none;"><span class="combodate"><select class="hour" style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value="5">05</option><option value="6">06</option><option value="7">07</option><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">222246</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option></select></span><span class="combodate"><select class="hour" style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value="5">05</option><option value="6">06</option><option value="7">07</选项><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option></select>&amp;nbsp;<select class="minute" style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value=5">05</选项><option value="6">06</option><option value="7">07</option><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</选项><option value="30">30</option><option value="31">31</option><option value="32">32</option><optionvalue="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><optionvalue="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><optionvalue="53">53

这部分表格是这样的(以红色突出显示):组合框表单

这是我正在使用的代码片段:

子图例()Dim IE 作为 InternetExplorerMedium将环境调暗为字符串将 webContent 调暗为字符串Dim NOME_EMPRESA、CNPJ、CPF、COD_ACESSO 作为字符串Dim Lookup_Range 作为范围昏暗的SH将确认框调暗为整数ConfirmationBox = MsgBox("Confirm Legendary Promotion changes to " & Worksheets("Legends").Range("E2") & "?", vbYesNoCancel, "Woolly Legends")'如果是点击确认框数据选择案例确认框案例 vb 是调暗每个IE'选择环境If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A2") ThenAD = ThisWorkbook.Sheets("Parameters").Range("C2").Value环境 = AD万一If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A3") ThenAC= ThisWorkbook.Sheets("Parameters").Range("C3").Value环境 = AC万一If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A4") ThenAB = ThisWorkbook.Sheets("Parameters").Range("C4").Value环境 = AB万一If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A5") Then测试 = ThisWorkbook.Sheets("Parameters").Range("C5").Value环境 = 测试万一设置 IE = 新的 SHDocVw.InternetExplorerIE.navigate 环境ShowWindow IE.hwnd, SW_MAXIMIZE做设置 SH = New Shell32.Shell对于 SH.Windows 中的每个 IE如果 InStr(1, eachIE.LocationURL, Environment) 那么设置 IE = eachIE'ie.Visible = False '这是因为在某些环境中，新进程默认为 Visible.退出做万一接下来每个IE环形设置 eachIE = 无设置 SH = 无'设置文档以允许通过 Excel 进行表单操作设置 doc = IE.document'为文本促销设置促销名称IE.document.all("promo_name").Value = ThisWorkbook.Sheets("Legends").Range("b3")IE.document.all("race_name").Value = ThisWorkbook.Sheets("Legends").Range("b19")'设置开始日期和结束日期IE.document.all("startdate").Value = Format$(ThisWorkbook.Sheets("Legends").Range("b7").Value, "yyyy-mm-dd")IE.document.all("enddate").Value = Format$(ThisWorkbook.Sheets("Legends").Range("b8").Value, "yyyy-mm-dd")'设置跑步者名称IE.document.all("runner_names_0").Value = ThisWorkbook.Sheets("Legends").Range("b20")IE.document.all("runner_names_1").Value = ThisWorkbook.Sheets("Legends").Range("b21")IE.document.all("runner_names_2").Value = ThisWorkbook.Sheets("Legends").Range("b22")IE.document.all("runner_names_3").Value = ThisWorkbook.Sheets("Legends").Range("b23")IE.document.all("runner_names_4").Value = ThisWorkbook.Sheets("Legends").Range("b24")IE.document.all("runner_names_5").Value = ThisWorkbook.Sheets("Legends").Range("b25")'为辖区设置下拉框If ThisWorkbook.Sheets("Legends").Range("B4").Value = "AAA" 或 ThisWorkbook.Sheets("Legends").Range("B5").Value = "AAB" 或 ThisWorkbook.Sheets("Legends").Range("B6").Value = "AAC" 然后IE.document.all("jurisdiction[]").Options(0).Selected = True万一If ThisWorkbook.Sheets("Legends").Range("B4").Value = "BBB" 或 ThisWorkbook.Sheets("Legends").Range("B5").Value = "BBA" 或 ThisWorkbook.Sheets("Legends").Range("B6").Value = "BBC" 然后IE.document.all("jurisdiction[]").Options(1).Selected = True万一If ThisWorkbook.Sheets("Legends").Range("B4").Value = "CCA" 或 ThisWorkbook.Sheets("Legends").Range("B5").Value = "CCB" 或 ThisWorkbook.Sheets("Legends").Range("B6").Value = "CCC" 然后IE.document.all("jurisdiction[]").Options(2).Selected = True万一结束选择结束子

我不确定如何将电子表格中的数据预填充到小时和分钟组合框中，因为它的布局与 html 中的其他字段略有不同.

任何帮助或指导将不胜感激.

谢谢！

解决方案

小时和分钟框是 select 元素，这意味着通过 InternetExplorer 自动化，您有两种主要方法来选择单个项目(和几个棘手情况的替代方案):

  1. 您可以在 select 本身上使用 selectedIndex 然后在该 select 下的列表中指定感兴趣的 option 索引代码>.我认为从 1 开始，但您需要检查.
  2. 在 option 元素本身上使用 .Selected = True.

由于您有具有相同值的选项，如果使用第二种方法，您需要通过父选择隔离下拉列表.这可以通过在前面添加父类名来完成.

方法一:

小时

ie.document.querySelector(".hour").SelectedIndex = 1

分钟

ie.document.querySelector(".minute").SelectedIndex = 1

方法二:

小时

ie.document.querySelector(".hour [value='0']").Selected = True 'hour 00

分钟

ie.document.querySelector(".minute [value='0']").Selected = True 'minute 00

替代方案主要是用 javascript 编写并通过以下方法执行的上述/变体ie.document.parentWindow.execScript.当元素由于某种原因不可交互但通常皱眉时，这会很有帮助尤其是 QA 自动化专家.

<小时>

阅读:

  1. CSS 选择器

<小时>

根据提供的 html 来区分 starttime 和 endtime，例如

ie.document.querySelector(".combodate + .combodate .hour [value='0']").Selected =True '结束时间ie.document.querySelector(".combodate + .combodate .minute [value='0']").Selected =True '结束分钟

使用变量

Dim var As Long无功 = 0ie.document.querySelector(".combodate .hour [value='" & cstr(var) & "']").Selected =True '开始时间

虽然您的 HTML 没有反映这一点，但我希望有更多类似的东西:

ie.document.querySelector("#starttime_field .combodate .hour [value='" & cstr(var) & "']").Selected =Trueie.document.querySelector("#endtime_field .combodate .hour [value='" & cstr(var) & "']").Selected =True

I am trying to prefill data from Excel to a local intranet website via VBA.

I've been able to prefill most data to the site, but I am struggling with this combodate box:

Please see HTML code:

<div class="form-group row  " id="starttime_field">
<label class="col-form-label col-md-3" for="starttime">Start time</label>
<div class="col-md-9">
<input type="text" id="starttime" name="starttime" class="form-control" size="30" style="display: none;"><span class="combodate">
<select class="hour " style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value="5">05</option><option value="6">06</option><option value="7">07</option><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option></select>&nbsp;:&nbsp;
<select class="minute " style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value="5">05</option><option value="6">06</option><option value="7">07</option><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option></select></span><span class="combodate"><select class="hour " style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value="5">05</option><option value="6">06</option><option value="7">07</option><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option></select>&nbsp;:&nbsp;<select class="minute " style="width: auto;"><option value="0">00</option><option value="1">01</option><option value="2">02</option><option value="3">03</option><option value="4">04</option><option value="5">05</option><option value="6">06</option><option value="7">07</option><option value="8">08</option><option value="9">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option></select></span>

This is what this section of the form looks like (highlighted in red): ComboBox Form

Here is a snippet of the code I am using:

Sub Legends()

Dim IE As InternetExplorerMedium
Dim Environment As String
Dim webContent As String
Dim NOME_EMPRESA, CNPJ, CPF, COD_ACESSO As String
Dim Lookup_Range As Range
Dim SH

Dim confirmationBox As Integer
confirmationBox = MsgBox("Confirm Legendary Promotion changes to " & Worksheets("Legends").Range("E2") & "?", vbYesNoCancel, "Woolly Legends")

'Confirmation box data if yes is clicked
Select Case confirmationBox
Case vbYes

Dim eachIE
    'Choosing the environment
    If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A2") Then
        AD = ThisWorkbook.Sheets("Parameters").Range("C2").Value
        Environment = AD
    End If
    If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A3") Then
        AC= ThisWorkbook.Sheets("Parameters").Range("C3").Value
        Environment = AC
    End If
    If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A4") Then
        AB = ThisWorkbook.Sheets("Parameters").Range("C4").Value
        Environment = AB
    End If
    If ThisWorkbook.Sheets("Legends").Range("E2").Value = ThisWorkbook.Sheets("Parameters").Range("A5") Then
        Testing = ThisWorkbook.Sheets("Parameters").Range("C5").Value
        Environment = Testing
    End If

    Set IE = New SHDocVw.InternetExplorer
    IE.navigate Environment
    ShowWindow IE.hwnd, SW_MAXIMIZE

    Do
      Set SH = New Shell32.Shell
      For Each eachIE In SH.Windows
        If InStr(1, eachIE.LocationURL, Environment) Then
          Set IE = eachIE
          'ie.Visible = False  'This is here because in some environments, the new process defaults to Visible.
          Exit Do
          End If
        Next eachIE
      Loop
    Set eachIE = Nothing
    Set SH = Nothing

    'Setting doc to allow for form manipulation via Excel
    Set doc = IE.document

    'Setting the Promotion Names for the Text Promotion
    IE.document.all("promo_name").Value = ThisWorkbook.Sheets("Legends").Range("b3")
    IE.document.all("race_name").Value = ThisWorkbook.Sheets("Legends").Range("b19")

    'Setting start date and end date
    IE.document.all("startdate").Value = Format$(ThisWorkbook.Sheets("Legends").Range("b7").Value, "yyyy-mm-dd")
    IE.document.all("enddate").Value = Format$(ThisWorkbook.Sheets("Legends").Range("b8").Value, "yyyy-mm-dd")

    'Setting the Runner Names
    IE.document.all("runner_names_0").Value = ThisWorkbook.Sheets("Legends").Range("b20")
    IE.document.all("runner_names_1").Value = ThisWorkbook.Sheets("Legends").Range("b21")
    IE.document.all("runner_names_2").Value = ThisWorkbook.Sheets("Legends").Range("b22")
    IE.document.all("runner_names_3").Value = ThisWorkbook.Sheets("Legends").Range("b23")
    IE.document.all("runner_names_4").Value = ThisWorkbook.Sheets("Legends").Range("b24")
    IE.document.all("runner_names_5").Value = ThisWorkbook.Sheets("Legends").Range("b25")

    'Setting the dropdown box for the jurisdictions
    If ThisWorkbook.Sheets("Legends").Range("B4").Value = "AAA" Or ThisWorkbook.Sheets("Legends").Range("B5").Value = "AAB" Or ThisWorkbook.Sheets("Legends").Range("B6").Value = "AAC" Then
        IE.document.all("jurisdiction[]").Options(0).Selected = True
    End If
    If ThisWorkbook.Sheets("Legends").Range("B4").Value = "BBB" Or ThisWorkbook.Sheets("Legends").Range("B5").Value = "BBA" Or ThisWorkbook.Sheets("Legends").Range("B6").Value = "BBC" Then
        IE.document.all("jurisdiction[]").Options(1).Selected = True
    End If
    If ThisWorkbook.Sheets("Legends").Range("B4").Value = "CCA" Or ThisWorkbook.Sheets("Legends").Range("B5").Value = "CCB" Or ThisWorkbook.Sheets("Legends").Range("B6").Value = "CCC" Then
        IE.document.all("jurisdiction[]").Options(2).Selected = True
    End If

End Select

End Sub

I am unsure how to pre-populate data from the spreadsheet into the hour and minute comboboxes, as the layout of this is a bit different from the other fields in the html.

Any help or guidance would be greatly appreciated.

Thanks!

解决方案

The hour and minute boxes are select elements which means with InternetExplorer automation you have two main methods for selecting individual items (and a couple of alternates for tricky situations):

  1. You can use selectedIndex on the select itself then specify index of option of interest in the list under that select. I think starts at 1 but you would need to check.
  2. Use .Selected = True on the option element itself.

As you have options that have the same values you need to isolate the dropdowns by the parent select if using second method. This can be done by adding parent className in front.

Method 1:

hour

ie.document.querySelector(".hour").SelectedIndex = 1

minute

ie.document.querySelector(".minute").SelectedIndex = 1

Method 2:

hour

ie.document.querySelector(".hour [value='0']").Selected = True  'hour 00

minute

ie.document.querySelector(".minute [value='0']").Selected = True 'minute 00

Alternates are predominately the above/variants thereof written in javascript and executed by method of ie.document.parentWindow.execScript. This can be helpful when elements are not interactable for some reason but generally frowned upon by QA automation specialists in particular.

Reading:

  1. CSS Selectors

EDIT:

To differentiate starttime from endtime based on html provided e.g.

ie.document.querySelector(".combodate + .combodate .hour [value='0']").Selected =True  'end hour
ie.document.querySelector(".combodate + .combodate .minute [value='0']").Selected =True  'end minute

Using variable

Dim var As Long
var = 0

ie.document.querySelector(".combodate .hour [value='" & cstr(var) & "']").Selected =True  'start hour

Although your HTML doesn't reflect this I would have expected something more like:

ie.document.querySelector("#starttime_field .combodate .hour [value='" & cstr(var) & "']").Selected =True 
ie.document.querySelector("#endtime_field .combodate .hour [value='" & cstr(var) & "']").Selected =True

这篇关于VBA 自动化 - 预填充组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆