VBA Automation-预填充组合框 [英] VBA Automation - Prefilling Combodate Box
问题描述
我正在尝试通过VBA将数据从Excel预填充到本地Intranet网站.
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:
请参阅HTML代码:
<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> :
<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> : <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
我不确定如何将电子表格中的数据预填充到小时和分钟组合框中,因为其布局与html中的其他字段有些不同.
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.
谢谢!
推荐答案
小时和分钟框是 select
元素,这意味着使用InternetExplorer自动化,您可以通过两种主要方法来选择单个项目(还有两种)棘手情况下的候补选择):
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):
- 您可以在
select
本身上使用selectedIndex
,然后在该select 下的列表中指定感兴趣的
option
的索引.代码>.我认为从1开始,但是您需要检查. - 对
option
元素本身使用.Selected = True.
- You can use
selectedIndex
on theselect
itself then specify index ofoption
of interest in the list under thatselect
. I think starts at 1 but you would need to check. - Use .Selected = True on the
option
element itself.
由于使用的选项具有相同的值,因此如果使用第二种方法,则需要通过父级选择来隔离下拉列表.这可以通过在前面添加父className来完成.
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.
方法1:
小时
ie.document.querySelector(".hour").SelectedIndex = 1
分钟
ie.document.querySelector(".minute").SelectedIndex = 1
方法2:
小时
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
.当元素由于某种原因无法交互但通常皱眉时,这可能会有所帮助尤其是由质量检查自动化专家进行.
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.
阅读:
根据提供的html来区分开始时间和结束时间
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
使用变量
Dim var As Long
var = 0
ie.document.querySelector(".combodate .hour [value='" & cstr(var) & "']").Selected =True 'start hour
尽管您的HTML不能反映这一点,但我本来希望更像是这样:
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 Automation-预填充组合框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!