使用IE VBA打开IE并与Web表单进行交互 [英] Open IE and Interact with Web Form using Excel VBA
本文介绍了使用IE VBA打开IE并与Web表单进行交互的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试创建一个Excel电子表格，该电子表格将具有登录页面，我可以在其中输入特定的星期数，期间数和/或年份，然后单击一个按钮以打开Internet Explorer实例，导航至网络表单，从电子表格的单元格中输入数据，然后加载所选的报告.
完成后，我希望能够加载各个报告包页面并将数据拉入电子表格中的单独选项卡中，但是每次只需一步，就可以使它正确加载交互.第一阶段...
以下是网络表单本身的代码
<br>
<body>
</body>
</html>
<head>
<!--include file="../../Connections/SQL.asp" -->
<title>Performance Reporting</title>
<link href="../../CSS/KPI_Style.css" rel="stylesheet" type="text/css">
<link href="../../CSS/KPI_Style.css" rel="stylesheet" type="text/css">
<link href="../CSS/KPI_Style.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style45 {color: #990000}
-->
</style>
<script type="text/javascript" language="javascript">
<!--
function makeDisable(){
var y=document.getElementById("store")
var x=document.getElementById("CHNSplits")
if (y.value=='CP11')
{
x.disabled=true
}
}
function makeEnable(){
var x=document.getElementById("CHNSplits")
x.disabled=false
}
-->
</script>
</head>
<body>
<div align="center">
<link href="../CSS/PL_Style.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style2 {font-size: 16px}
.style33 {color: #ffffff}
-->
</style>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#001446" class="border9"><div align="left"><img src="80.gif" alt="DSGi" /></div></td>
<td valign="bottom" bgcolor="#001446" class="border16"><div align="right" class="xbig style1 style33"><span class="style2">Store Performance</span> </div></td>
</tr>
</table>
<br>
<br>
<style type="text/css">
<!--
body,td,th {
font-family: Arial, Helvetica, sans-serif;
}
body {
background-image: url();
}
.style1 {
color: #d33;
font-size: 18px;
}
.style2 {color: #000000; font-size: 2px; }
.style10 {color: #d33}
.style15 {font-size: 12px}
-->
</style>
<title></title>
</div>
<link href="../../../Testing/KPI/NewKPI/Digital_KPI/stylesheet.css" rel="stylesheet" type="text/css">
<div align="center"><span class="reportname"> Reporting Selection</span></div>
<form name="form1" id="form1" method="post" action="Get_Page_V4.asp">
<input name="store" type="hidden" value="2397" >
<input name="ViewType" type="hidden" value="store" >
<table width="60%" border="1px" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000">
<td colspan="2"><div align="center" class="formnamesmall">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr height="4">
<td></td>
</tr>
<tr height="5">
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td colspan="6"><div align="center" class="formtextverylarge">Selected Location:</span> 1111</div></td>
</tr>
<tr>
<td><input name="RadioGroup1" type="radio" value="1" checked></td>
<td class="formnamesmall" ><strong>Yesterday</td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr class="formtext">
<td><input type="radio" name="RadioGroup1" value="2"></td>
<td class="formnamesmall" ></td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall"> </td>
<td class="formtext">
<input name="datebox" type="hidden" id="datebox" value="07/10/2018" size="10" maxlength="10"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr>
<td><input type="radio" name="RadioGroup1" value="3"></td>
<td class="formnamesmall" ><strong>Week</td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Week </td>
<td class="formtext">
<input name="week" type="text" id="week" value="24" size="2" maxlength="2"></td>
</tr>
<tr>
<td> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Financial Year </td>
<td class="formtext">
<input name="weekyear" type="text" id="weekyear" value="2019" size="4" maxlength="4"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr class="formtext">
<td><label>
<input type="radio" name="RadioGroup1" value="4">
</label></td>
<td class="formnamesmall" ><strong>Period</td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Period </td>
<td class="formtext">
<input name="period" type="text" id="period" value="6" size="2" maxlength="2"></td>
</tr>
<tr class="formtext">
<td> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Financial Year </td>
<td class="formtext">
<input name="periodyear" type="text" id="periodyear" value="2019" size="4" maxlength="4"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr class="formtext">
<td><input type="radio" name="RadioGroup1" value="6"></td>
<td class="formnamesmall" ><strong>Year </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Financial Year </td>
<td class="formtext">
<input name="YearYear" type="text" id="YearYear" size="4" maxlength="4" value="2019" ></td>
</tr>
<tr><td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
</tr>
<!--
<tr>
<td colspan="4"><div align="center"><span class="formnamesmall style45">Select Report Type</span></div></td>
<td> </td>
<td colspan="2" class="formname" ><strong></td>
</tr>
-->
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<tr>
<!--
<td><div align="center">
<input name="RadioGroup2" type="radio" class="Style10" value="1" >
</div></td>
<td>
<div align="right" class="formnamesmall style45">
<div align="left">Charts </div>
</div></td>
-->
<input name="RadioGroup2" type="hidden" class="Style10" value="6" checked="Checked">
<!--
<td><div align="right" class="formnamesmall">
<div align="left" class="style45">League Tables
</div>
</div></td>
-->
<td class="formnamesmall" colspan="4"> </td>
<td><input type="submit" name="Submit" value="Display Report"></td>
</tr>
</table>
<p> </p>
</div></td>
</tr>
</table>
<input name="page_name" type="hidden" value="Region_Page1_" />
</form>
<br>
<br>
<style type="text/css">
<!--
.style1 {
font-size: 10px;
color: #FFFFFF;
}
-->
</style>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td valign="bottom" bgcolor="#001446"><div align="right" class="xbig style1 style1"> </div></td>
</tr>
</table>
</body>
然后下面是我到目前为止尝试过的VBA代码
Sub Get_Data()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim HWNDSrc As Long
Dim dates As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.navigate "http://INTRANETSITE/Summary_Select.asp"
Do Until .readyState = 4 And Not .Busy
DoEvents
Loop
.document.all.Item("radioGroup1")(3).Checked = True 'Select the 4th checkbox down on the list
.document.forms(0).submit 'Submit the form to generate the result
End With
Pause (2)
With IE
.navigate "http://INTRANETSITE/ReportDetail2.asp?"
Do Until .readyState = 4 And Not .Busy
DoEvents
Loop
End With End Sub
上面的代码绝对没有问题，可以导航到Web表单，并且确实选择了第四个单选按钮(用于期间报告)，然后提交表单并显示正确的第一页.
但是，我不确定如何与文本框进行交互以修改我要为其选择报告的星期或期间，而且，当它尝试导航到ReportDetail2.asp时，它只会返回以下错误:
对象"IWebBrowser2"的方法导航"失败
然后使VBA代码崩溃?正如我前面提到的，这样做的长远目标是能够浏览20多页的报告，将每个表中的数据拉到电子表格的标签中，以便即使在报告之间也无法导航，甚至在我们获取代码以提取数据之前，这是一个问题.
希望你们能帮助我解决这个问题吗?
谢谢
解决方案
尝试以下类似方法.我正在显示您可能会考虑的其他选项，因此请记住将您不想使用的选项注释掉.
您需要通过VBE>工具>引用添加对Microsoft Internet控件的引用，或使用
的后期绑定调用Dim ie As New InternetExplorer : Set ie = CreateObject("InternetExplorer.Application")
VBA:
Option Explicit
Public Sub MakeSelections()
Dim ie As New InternetExplorer
Const URL As String = "yourURL"
With ie
.Visible = True
.navigate URL
While .Busy Or .readyState < 4: DoEvents: Wend
With .document
.querySelector("[value='1']").Checked = True 'Yesterday. CSS attribute = value selector
.querySelector("[value='3']").Checked = True 'Week
.querySelector("[value='4']").Checked = True 'Period
.querySelector("[value='6']").Checked = True 'Year
.getElementById("week").Value = 6 'week value
.getElementById("weekyear").Value = 2018 'financial year value
.getElementById("period").Value = 6 'period value
.getElementById("periodyear").Value = 2018 'period financial year value
.getElementById("yearyear").Value = 2019 'financial year value
.querySelector("[value='Display Report']").Click 'display report
End With
Stop '<== Delete me later
'.Quit '<== Remember to quit application
End With
End Sub
I'm trying to create an excel spreadsheet that will have a landing page where I can input a specific week number, period number and/or year then click a button which will open an instance of Internet Explorer, navigate to the web form, input the data from the cells in the spreadsheet and then load up the reports selected.
Once this is complete, I then want to be able to load through the various reporting pack pages and pull the data into separate tabs in the spreadsheet, but one step at a time... getting it to load an interact properly is the first stage...
Below is the code for the web form itself
<br>
<body>
</body>
</html>
<head>
<!--include file="../../Connections/SQL.asp" -->
<title>Performance Reporting</title>
<link href="../../CSS/KPI_Style.css" rel="stylesheet" type="text/css">
<link href="../../CSS/KPI_Style.css" rel="stylesheet" type="text/css">
<link href="../CSS/KPI_Style.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
.style45 {color: #990000}
-->
</style>
<script type="text/javascript" language="javascript">
<!--
function makeDisable(){
var y=document.getElementById("store")
var x=document.getElementById("CHNSplits")
if (y.value=='CP11')
{
x.disabled=true
}
}
function makeEnable(){
var x=document.getElementById("CHNSplits")
x.disabled=false
}
-->
</script>
</head>
<body>
<div align="center">
<link href="../CSS/PL_Style.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style2 {font-size: 16px}
.style33 {color: #ffffff}
-->
</style>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#001446" class="border9"><div align="left"><img src="80.gif" alt="DSGi" /></div></td>
<td valign="bottom" bgcolor="#001446" class="border16"><div align="right" class="xbig style1 style33"><span class="style2">Store Performance</span> </div></td>
</tr>
</table>
<br>
<br>
<style type="text/css">
<!--
body,td,th {
font-family: Arial, Helvetica, sans-serif;
}
body {
background-image: url();
}
.style1 {
color: #d33;
font-size: 18px;
}
.style2 {color: #000000; font-size: 2px; }
.style10 {color: #d33}
.style15 {font-size: 12px}
-->
</style>
<title></title>
</div>
<link href="../../../Testing/KPI/NewKPI/Digital_KPI/stylesheet.css" rel="stylesheet" type="text/css">
<div align="center"><span class="reportname"> Reporting Selection</span></div>
<form name="form1" id="form1" method="post" action="Get_Page_V4.asp">
<input name="store" type="hidden" value="2397" >
<input name="ViewType" type="hidden" value="store" >
<table width="60%" border="1px" align="center" cellpadding="0" cellspacing="0" bordercolor="#000000">
<td colspan="2"><div align="center" class="formnamesmall">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr height="4">
<td></td>
</tr>
<tr height="5">
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td colspan="6"><div align="center" class="formtextverylarge">Selected Location:</span> 1111</div></td>
</tr>
<tr>
<td><input name="RadioGroup1" type="radio" value="1" checked></td>
<td class="formnamesmall" ><strong>Yesterday</td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr class="formtext">
<td><input type="radio" name="RadioGroup1" value="2"></td>
<td class="formnamesmall" ></td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall"> </td>
<td class="formtext">
<input name="datebox" type="hidden" id="datebox" value="07/10/2018" size="10" maxlength="10"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr>
<td><input type="radio" name="RadioGroup1" value="3"></td>
<td class="formnamesmall" ><strong>Week</td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Week </td>
<td class="formtext">
<input name="week" type="text" id="week" value="24" size="2" maxlength="2"></td>
</tr>
<tr>
<td> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Financial Year </td>
<td class="formtext">
<input name="weekyear" type="text" id="weekyear" value="2019" size="4" maxlength="4"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr class="formtext">
<td><label>
<input type="radio" name="RadioGroup1" value="4">
</label></td>
<td class="formnamesmall" ><strong>Period</td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Period </td>
<td class="formtext">
<input name="period" type="text" id="period" value="6" size="2" maxlength="2"></td>
</tr>
<tr class="formtext">
<td> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Financial Year </td>
<td class="formtext">
<input name="periodyear" type="text" id="periodyear" value="2019" size="4" maxlength="4"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr height="15">
<td height="10"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
<td height="10" class="formtext"></td>
</tr>
<tr class="formtext">
<td><input type="radio" name="RadioGroup1" value="6"></td>
<td class="formnamesmall" ><strong>Year </td>
<td class="formtext"> </td>
<td class="formtext"> </td>
<td class="formnamesmall">Enter Financial Year </td>
<td class="formtext">
<input name="YearYear" type="text" id="YearYear" size="4" maxlength="4" value="2019" ></td>
</tr>
<tr><td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
</tr>
<!--
<tr>
<td colspan="4"><div align="center"><span class="formnamesmall style45">Select Report Type</span></div></td>
<td> </td>
<td colspan="2" class="formname" ><strong></td>
</tr>
-->
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<td height="10"> </td>
<tr>
<!--
<td><div align="center">
<input name="RadioGroup2" type="radio" class="Style10" value="1" >
</div></td>
<td>
<div align="right" class="formnamesmall style45">
<div align="left">Charts </div>
</div></td>
-->
<input name="RadioGroup2" type="hidden" class="Style10" value="6" checked="Checked">
<!--
<td><div align="right" class="formnamesmall">
<div align="left" class="style45">League Tables
</div>
</div></td>
-->
<td class="formnamesmall" colspan="4"> </td>
<td><input type="submit" name="Submit" value="Display Report"></td>
</tr>
</table>
<p> </p>
</div></td>
</tr>
</table>
<input name="page_name" type="hidden" value="Region_Page1_" />
</form>
<br>
<br>
<style type="text/css">
<!--
.style1 {
font-size: 10px;
color: #FFFFFF;
}
-->
</style>
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td valign="bottom" bgcolor="#001446"><div align="right" class="xbig style1 style1"> </div></td>
</tr>
</table>
</body>
And then below is the VBA code I have tried so far
Sub Get_Data()
Dim i As Long
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim HWNDSrc As Long
Dim dates As Object
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.navigate "http://INTRANETSITE/Summary_Select.asp"
Do Until .readyState = 4 And Not .Busy
DoEvents
Loop
.document.all.Item("radioGroup1")(3).Checked = True 'Select the 4th checkbox down on the list
.document.forms(0).submit 'Submit the form to generate the result
End With
Pause (2)
With IE
.navigate "http://INTRANETSITE/ReportDetail2.asp?"
Do Until .readyState = 4 And Not .Busy
DoEvents
Loop
End With End Sub
The code above navigates to the web form absolutely no problem and does select the 4th radio button which is for period reports, then submits the form and displays the correct first page.
However, I am unsure how to interact with the text boxes to also amend which week or period I want to select reports for, and also, when it tries to navigate to ReportDetail2.asp it just returns the following error:
Method 'Navigate' of object 'IWebBrowser2' failed
and then crashes the VBA code? And as I mentioned earlier, the long term goal of this is to be able to navigate through 20+ pages of reports, pulling the data from each table into a tab on the spreadsheet, so if it can't navigate between the reports, even before we get to the code to pull the data in, then that's a problem.
Hope you guys can help me get this solved?
Thanks
解决方案
Try something like the following. I am showing the different options you might consider so remember to comment out those you don't want to use.
You need to add a reference to Microsoft Internet Controls via VBE > Tools > References or use late bound call of
Dim ie As New InternetExplorer : Set ie = CreateObject("InternetExplorer.Application")
VBA:
Option Explicit
Public Sub MakeSelections()
Dim ie As New InternetExplorer
Const URL As String = "yourURL"
With ie
.Visible = True
.navigate URL
While .Busy Or .readyState < 4: DoEvents: Wend
With .document
.querySelector("[value='1']").Checked = True 'Yesterday. CSS attribute = value selector
.querySelector("[value='3']").Checked = True 'Week
.querySelector("[value='4']").Checked = True 'Period
.querySelector("[value='6']").Checked = True 'Year
.getElementById("week").Value = 6 'week value
.getElementById("weekyear").Value = 2018 'financial year value
.getElementById("period").Value = 6 'period value
.getElementById("periodyear").Value = 2018 'period financial year value
.getElementById("yearyear").Value = 2019 'financial year value
.querySelector("[value='Display Report']").Click 'display report
End With
Stop '<== Delete me later
'.Quit '<== Remember to quit application
End With
End Sub
这篇关于使用IE VBA打开IE并与Web表单进行交互的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
