使用VBA从安全网站导入excel中的数据时出现运行时错误438 [英] Runtime error 438 while importing data in excel from secured website using VBA
本文介绍了使用VBA从安全网站导入excel中的数据时出现运行时错误438的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用以下VBA代码（从其他网站下载）从网站导入MS excel中的一些数据（登录ID和密码已提供给我）
I am trying to import some data in MS excel from a website (login ID & password have been provided to me) using following VBA code (downloaded from other website)
Sub GetTable()
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject
'create a new instance of ie
Set ieApp = New InternetExplorer
'you don’t need this, but it’s good for debugging
ieApp.Visible = True
'assume we’re not logged in and just go directly to the login page
ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/LoginAction.do?hmode=loginPage"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Set ieDoc = ieApp.Document
'fill in the login form – View Source from your browser to get the control names
With ieDoc.forms(0)
.userType.Value = "1"
.userId.Value = "rlbdgs"
.userPassword.Value = "123"
.submit
End With
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
'now that we’re in, go to the page we want
ieApp.Navigate "http://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/LoginAction.do?hmode=login"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
'get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item("sampletable")
'copy the tables html to the clipboard and paste to teh sheet
If Not ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText "<html>" & ieTable.outerHTML & "</html>"
clip.PutInClipboard
Sheet1.Select
Sheet1.Range("A1").Select
Sheet1.PasteSpecial "Unicode Text"
End If
'close 'er up
ieApp.Quit
Set ieApp = Nothing
End Sub
虽然我正在尝试运行代码，但我在这个区域收到运行时错误438对象不支持此属性或方法
While i am trying to run the Code, I am getting 'Runtime error 438 Object doesn't support this property or method' at this area
With ieDoc.forms(0)
.userType.Value = "1"
.userId.Value = "rlbdgs"
.userPassword.Value = "123"
.submit
End With
来源登录页面的代码如下：
Source code of login page is as under:
<html>
<head>
<title>Report User Login</title>
<link href="../styles/loginStyle.css" rel="stylesheet" type="text/css" />
<script src="../scripts/jquery-1.11.0.js"></script>
<script type="text/javascript">
javascript:window.history.forward(1);
function trim(str)
{
return str.replace(/^\s*|\s*$/g,"");
}
function getUserType()
{
if(typeof(Storage)!=="undefined")
{
if (localStorage.userType)
{
document.forms[0].userType[localStorage.userType].checked = true ;
if (localStorage.userType == '0')
{
document.forms[0].userId.value="";
document.forms[0].userPassword.value="";
}
else
{
document.forms[0].userId.value="";
document.forms[0].userPassword.value="";
}
}
else
{
document.forms[0].userType[0].checked = true ;
document.forms[0].userId.value="";
document.forms[0].userPassword.value="";
}
}
else
{
localStorage.userType = "0";
//document.getElementById("result").innerHTML="Sorry, your browser does not support web storage...";
//alert("Sorry, your browser does not support web storage...");
}
}
function setUserType(thisObj)
{
localStorage.userType = thisObj.value;
if (localStorage.userType == '0')
{
document.forms[0].userId.value="";
document.forms[0].userPassword.value="";
}
else
{
document.forms[0].userId.value="";
document.forms[0].userPassword.value="";
}
}
function callAction()
{
document.forms[0].action ="../../JSP/rpt/LoginAction.do?hmode=login";
document.forms[0].submit();
}
</script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link href="../styles/links.css" rel="stylesheet" type="text/css">
<link href="../styles/text.css" rel="stylesheet" type="text/css">
</head>
<body onLoad="getUserType();" bgcolor="#e3e3e3" >
<script type="text/javascript">
<!--
//alert('screen.width:'+screen.width)
if (screen.width < 499 || screen.height < 499) {
alert('Your Resolution: ' + screen.width + ' X ' + screen.height + '. This Application can only be run on Resolution: >= 499 X 499.')
document.location = "/M";
}
//-->
</script>
<form name="reportLoginForm" method="post" action="/CMSREPORT/JSP/rpt/LoginAction.do">
<table width="800" height="600" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="800" height="2" align="center" valign="top" bgcolor="#FFFFFF"></td>
</tr>
<tr>
<td height="" align="left" valign="top" bgcolor="#FFFFFF"><table width="800" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="1" align="left" valign="top" bgcolor="#FFFFFF"> </td>
<td width="497" align="left" valign="top" bgcolor="#FFFFFF"><img width="497" height="200" src="images/backimage.jpg"/> </td>
<td width="1" align="left" valign="top" bgcolor="#FFFFFF"> </td>
<td align="left" valign="top" bgcolor="#FFFFFF"><table width="300" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="30" align="center" valign="middle" bgcolor="#333333"> <font color="white" size="4">CMS REPORT LOGIN</font>
</td>
</tr>
<tr>
<td height="170" align="left" valign="top"><form>
<table width="300" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="35" colspan="3" align="left" valign="middle">
<input type="radio" name="userType" value="0" onclick="setUserType(this);"><i>MOBILE/REPORT USER</i>
<br>
<input type="radio" name="userType" value="1" onclick="setUserType(this);"><i>LOBBY/DIVISION/ZONE USER</i>
<br>
<input type="radio" name="userType" value="2" onclick="setUserType(this);"><i>KIOSK CREW LOGIN</i>
</td>
</tr>
<tr>
<td height="17" colspan="3" align="left" valign="top"></td>
</tr>
<tr>
<td width="80" align="center" valign="middle"><i><b>USER ID:</b></i></td>
<td width="2" align="left" valign="top"> </td>
<td width="200" align="left" valign="top"><input type="text" name="userId" value=""></td>
</tr>
<tr>
<td height="15" colspan="3" align="left" valign="top"> </td>
</tr>
<tr>
<td align="center" valign="middle"><i><b>PASSWORD:</b></i></td>
<td align="left" valign="top"> </td>
<td align="left" valign="top"><input type="password" name="userPassword" value=""></td>
</tr>
<tr>
<td colspan="3" align="left" valign="top"><b><i><font color="red"><br/>
</font>
</i>
</b><script type="text/javascript">document.write('Resolution: '+screen.width+' X '+screen.height)</script>
<a href="/CMSTEST"></a></td>
</tr>
<tr>
<td align="left" valign="top" bgcolor="#333333"> </td>
<td align="left" valign="top" bgcolor="#333333"> </td>
<td height="36" align="center" valign="middle" bgcolor="#333333"><input type="submit" name="hmode" value="Login" onclick="return callAction();"></td>
</tr>
</table>
</form></td>
</tr>
</table></td>
<td width="1" align="left" valign="top" bgcolor="#FFFFFF"></td>
</tr>
<tr>
<td align="left" valign="top" bgcolor="#FFFFFF"></td>
<td height="2" colspan="3" align="left" valign="top" bgcolor="#FFFFFF"></td>
<td align="left" valign="top" bgcolor="#FFFFFF"></td>
</tr>
<tr>
<td align="left" valign="top" bgcolor="#FFFFFF"></td>
<td colspan="3" align="left" valign="top" bgcolor="#FFFFFF"><table width="798" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="798" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="300"><table width="300" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="300" height="50" align="left" valign="top"><img src="images/news.jpg" /></td>
</tr>
<tr>
<td height="150" align="left" valign="top"><table width="300" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="160" align="left" valign="top" bgcolor="#CCCCCC"><div id="sld">
<marquee behavior="scroll" width="300px;" height="150px;" scrollamount="3" direction="up">
Welcome to CMS Report Login Page. Now you have to enter user id and password to see CMS Reports.
</marquee>
</div></td>
</tr>
</table></td>
</tr>
</table></td>
<td width="2"> </td>
<td align="center" valign="top"><h1 class="text" align="center"><b>Welcome To CMS</b></h1>
<p class="text">CMS project was started in 2005 with a dedicated team of 5 fresh software developers. With the passage of time the CMS system has bloomed out with the support of over 25 dedicated software engineers contributing in it's development. Leaders in the software industry like IBM, HCL, ITI, Redhat, MIND, Neoware, VXL, Anologics, Parrus IT, Palas, 3M have been contributing their resources in it's development.
At present the software has Freight, Shunter, Coaching, SMS, Loco Inspector, QUICK and many more modules in use. New immerging technologies and challenges like Remote Management of the thin client from a centralised location and time syncing of all the clients to one time standard have been adopted in the CMS system.
The project has been spread out to entire Indian Railways with nearly 300 lobby location and is used by over one lakh users regularly. The team has taken a leap forward by launching Electrical Loco management software in June 2009 on Jhansi Division. The Electrical Loco management software is being developed by the same team and is likely to be fully developed by June 2010.
</p>
</td>
</tr>
</table></td>
</tr>
</table></td>
<td align="left" valign="top" bgcolor="#FFFFFF"> </td>
</tr>
</table></td>
</tr>
</table>
</form>
</body>
</html>
VBA代码中需要什么更改？请帮助。
What changes required in VBA code? Please help.
推荐答案
尝试这个（TRIED AND TESTED）
Try this (TRIED AND TESTED)
替换
With ieDoc.forms(0)
.userType.Value = "1"
.UserId.Value = "rlbdgs"
.userPassword.Value = "123"
.submit
End With
与
With ieDoc
.getElementById("userId").setAttribute "value", "rlbdgs"
.getElementById("userPassword").setAttribute "value", "123"
'~~> This will select the 2nd radio button as it is `0` based
.getElementsByName("userType")(1).Checked = True
.getElementById("hmode").Click
End With
这篇关于使用VBA从安全网站导入excel中的数据时出现运行时错误438的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
