无法使用VB代码从另一个网站导入excel中的数据 [英] Unable to import data in excel from another website using VB code
本文介绍了无法使用VB代码从另一个网站导入excel中的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
'根据表的id获取表
设置ieDoc = ieApp.Document
设置ieTable = ieDoc.all.Item（report-table）
'将表格html复制到剪贴板并粘贴到工作表
如果不是ieTable是没有
设置剪辑= New DataObject
clip.SetText< html> &安培; ieTable.outerHTML& < / HTML> 中
clip.PutInClipboard
Sheet1.Select
Sheet1.Range（A1）。选择
Sheet1.PasteSpecialUnicode Text
End If
完整的代码如下：
Sub GetTable（）
Dim ieApp As InternetExplorer
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject
'创建一个新的实例ie
设置ieApp =新的InternetExplorer
'你不需要这个，但它是好的调试
ieApp.Visible = True
'假设我们没有登录，直接进入登录页面
ieApp.Navigatehttp://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/LoginAction。 do？hmode = loginPage
Do While ieApp.Busy：DoEvents：Loop
Do Untable ieApp.ReadyState = READYSTATE_COMPLETE：DoEvents：Loop
设置ieDoc = ieApp.Document
'填写登录fo rm - 从浏览器中查看源代码以获取控制名称
使用ieDoc
.getElementById（userId）。setAttributevalue，rlbdgs
.getElementById（userPassword）。 setAttributevalue，123
'~~>这将选择第二个单选按钮，因为它是基于
.getElementsByName（userType）（1）.Checked = True
.getElementById（hmode）。点击
结束
尽管ieApp.Busy：DoEvents：循环
做直到ieApp.ReadyState = READYSTATE_COMPLETE：DoEvents：循环
'现在我们在，去到我们想要的页面
ieApp.Navigatehttp://cms.indianrail.gov.in/CMSREPORT/JSP/rpt/GeneralReportAction.do?hmode=drillDown25And26And30GeneralReport&kioskOrManual=K&val=26&wherePart=ZONE_CODE_C = -IR-& lobby = AJJ& type = B& startDate =& endDate =&牵引= ELEC
尽管ieApp.Busy：DoEvents：Loop
直到ieApp.ReadyState = READYSTATE_COMPLETE ：DoEvents：Loop
'根据表的id获取表
设置ieDoc = ieApp.Document
设置ieTable = ieDoc.all.Item（report-table）
'将表html复制到剪贴板并粘贴到工作表
如果否t ieTable Is Nothing Then
Set clip = New DataObject
clip.SetText< html> &安培; ieTable.outerHTML& < / HTML> 中
clip.PutInClipboard
Sheet1.Select
Sheet1.Range（A1）。选择
Sheet1.PasteSpecialUnicode Text
End If
'close'er up
ieApp.Quit
设置ieApp = Nothing
End Sub
包含表格的网页的源代码（从要下拉的数据）为
< HTML>
< head>
< title> CREW BOOKED ON TA< / title>
< link href =../ styles / reportStyle.css =stylesheettype =text / css/>
< meta http-equiv =Content-Typecontent =text / html; charset = iso-8859-1/>
< script type =text / javascript>
函数DoNav（theUrl）
{
// alert（theUrl）;
document.location.href = theUrl;
}
< / script>
< / head>
< body>
< table id =report-table>
<！ - 表头 - >
< thead>
< tr>
< th scope =colclass =datestyle =border：0px; colspan =10>打印日期时间：< span> 14-08-2014 13：30< / span>< / th>
< / tr>
< tr>
< th scope =colclass =report-crisstyle =text-align：center;> CRIS< / th>< th scope =colclass =report-heading style =text-align：center; colspan =8>在LOCO中的VIGILENCE控制设备（VCD）不工作（通过KIOSK注册）（LOCO SHED WISE）（最近24小时）< th scope =colclass =report-crisstyle = 文本对齐：中心; > CMS< /第>
< / tr>
< tr style =border：none;>
< th colspan =9style =border-right：none;>
< span class =report-buttononclick =javascript：history.back（）;> BACK< / span>
< span class =report-buttononclick =javascript：window.print（）;> PRINT< / span>
< / th>
< th style =border-left：none; text-align：right;>< / th>
< / tr>
< / table>
< table id =report-table>
< thead>
< tr style =border：none;对齐= 中心 >
< th> S.No.< / th>
< th> ID< / th>
< th> NAME< / th>
< th> SIGNOFF DATE< / th>
< th> FROM< / th>
< th> TO< / th>
< th> LOCO NO。< / th>
< BASE SHED< / th>
< th> RAILWAY< / th>
< / tr>
< / thead>
< tbody>
< tr>
< td> 1< / td>
< td> BINA1482< / td>
< td> RAKESH KUMAR BAJPAI< / td>
< td> 14-08-2014 11：07< / td>
< td> BINA< / td>
< td> ET< / td>
< td> 23551< / td>
< td> BRC< / td>
< td> WR< / td>
< / tr>
< / tbody>
< / table>
*如果此报告的持续时间是过去24小时或从迄今为止的持续时间相同，则只会显示该站点的最后一次VCD报告。
< / body>
< / html>
请再次帮助。
解决方案
通过替换
设置ieTable = ieDoc.all.Item （报表）
与
设置ieTable = ieDoc.getElementById（report-table）
看起来Item项返回一个DispHtmlElementCollection - 不支持outerHtml属性。 getElementById返回一个HTMLTable元素
I am try to import some data in excel from a website using VB code. During course of testing first got error 438 in login section which was rectified by respected Mr. Siddharth Rout (Thanks again) through my earlier question Runtime error 438 while importing data in excel from secured website using VBA. Now i am facing error 438 in following section and unable to import data / excel sheet remain blank.
'get the table based on the table’s id
Set ieDoc = ieApp.Document
Set ieTable = ieDoc.all.Item("report-table")
'copy the tables html to the clipboard and paste to the 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
Complete code is as under:
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
.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
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/GeneralReportAction.do?hmode=drillDown25And26And30GeneralReport&kioskOrManual=K&val=26&wherePart=ZONE_CODE_C=-IR-&lobby=AJJ&type=B&startDate=&endDate=&traction=ELEC"
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("report-table")
'copy the tables html to the clipboard and paste to the 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
Source code of webpage containing table (from data to be downloded) is as
<html>
<head>
<title>CREW BOOKED ON TA</title>
<link href="../styles/reportStyle.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<script type="text/javascript">
function DoNav(theUrl)
{
//alert(theUrl);
document.location.href = theUrl;
}
</script>
</head>
<body>
<table id="report-table">
<!-- Table header -->
<thead>
<tr>
<th scope="col" class="date" style="border:0px;" colspan="10">Print Date Time: <span>14-08-2014 13:30</span></th>
</tr>
<tr>
<th scope="col" class="report-cris" style="text-align:center;">CRIS</th><th scope="col" class="report-heading" style="text-align:center;" colspan="8">VIGILENCE CONTROL DEVICE (VCD) IN LOCO NOT WORKING(SIGN OFF THROUGH KIOSK)(LOCO SHED WISE)(LAST 24 HOURS)<th scope="col" class="report-cris" style="text-align:center;">CMS</th>
</tr>
<tr style="border:none;">
<th colspan="9" style="border-right:none;">
<span class="report-button" onclick="javascript:history.back();">BACK</span>
<span class="report-button" onclick="javascript:window.print();">PRINT</span>
</th>
<th style="border-left:none;text-align:right;"></th>
</tr>
</table>
<table id="report-table">
<thead>
<tr style="border:none;" align="center">
<th>S.No.</th>
<th>ID</th>
<th>NAME</th>
<th>SIGNOFF DATE</th>
<th>FROM</th>
<th>TO</th>
<th>LOCO NO.</th>
<th>BASE SHED</th>
<th>RAILWAY</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>BINA1482</td>
<td >RAKESH KUMAR BAJPAI</td>
<td>14-08-2014 11:07</td>
<td >BINA</td>
<td>ET </td>
<td>23551 </td>
<td>BRC</td>
<td>WR </td>
</tr>
</tbody>
</table>
* If duration for this report is last 24 hours or from and to date is same, then only last VCD reporting of the loco will be shown.
</body>
</html>
Please help once again.
解决方案
I've got this to work by replacing
Set ieTable = ieDoc.all.Item("report-table")
with
Set ieTable = ieDoc.getElementById("report-table")
Looks like the Item method returns a DispHtmlElementCollection - that doesn't support the outerHtml property. The getElementById instead returns an HTMLTable element
这篇关于无法使用VB代码从另一个网站导入excel中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
