Board logo

标题: [问题求助] 【己解决】求大神帮忙编写一个VBS文件提取网页内容 [打印本页]
作者: boyangcoco    时间: 2015-4-18 19:57     标题: 【己解决】求大神帮忙编写一个VBS文件提取网页内容

求大神帮忙编写一个VBS文件提取网页内容:

网页上有类似表格的页面,想求一个VBS来显示计算网页上的加班信息,
具体提取内容:
<td class="nbtext" width="70px">06:52</td>
<td class="nbtext" width="70px">06:52</td>[attach]8550[/attach]
<td width="70px" class="nbtext"><FONT color=white>工时类别</FONT></td>
里的时间"06:82","3.00","工时类别" 这些内容(双引号内的内容) 用来计算下面几个项目.

详细内容:
可以计算出:
缺勤: ????
迟到: ????
早退: ????
实际工作时间: ???
平日加班: ????
公休日加班: ????
加班单小时1:  ???
审核加班小时1 ???
休假类别+休假小时: ????

贴出Html原文件:
作者: yu2n    时间: 2015-4-18 23:22

本帖最后由 yu2n 于 2015-4-19 10:34 编辑

1. 提取内容没有问题。
2. 计算几个项目——这个就有点强人所难。毕竟贵公司考勤算法，不是谁都能完全猜得对的。题主需要提供考勤算法。

以下代码供参考，将提取网页 "Attendence Detail.htm"中"tblDetail"表格的所有内容。
  1. htmlfile = "Attendence Detail.htm"

  2. 

  3. Dim fso, sHtml, oHTML

  4. Set fso = CreateObject("Scripting.FilesystemObject")

  5. Set oHTML = CreateObject("htmlfile")

  6. 

  7. sHtml = fso.OpenTextFile(htmlfile, 1, False, 0).ReadAll

  8. oHTML.DesignMode = "on"     ' 开启编辑模式，所有js都不会被执行

  9. oHTML.Write sHtml           ' 写入数据

  10. 

  11. 'MsgBox objHTML.body.InnerHTML

  12. 

  13. Dim oTable, sTable, nRow, nCol, sLine, sCell

  14. Set oTable = oHTML.getElementById("tblDetail")

  15. 

  16. If Not oTable Is Nothing Then

  17. 

  18.   For nRow = 0 To oTable.rows.length - 1

  19.     For nCol = 0 To oTable.rows(nRow).cells.length- 1

  20.       sCell = oTable.rows(nRow).cells(nCol).innerText

  21.       If sLine <> "" Then sLine = sLine & ","

  22.       sLine = sLine & sCell

  23.       sCell = ""

  24.     Next

  25.     If sTable <> "" Then sTable = sTable & vbCrLf

  26.     sTable = sTable & sLine

  27.     sLine = ""

  28.   Next

  29.   

  30. End If

  31. 

  32. fso.OpenTextFile(htmlfile & ".log", 2, True, 0).Write sTable

  33. MsgBox sTable
复制代码
结果如下：
  1. 日期,日期类型,进1,出1,进2,出2,进3,出3,进4,出4,工时类别,缺勤,迟到,早退,实际工作时间,加班类别1,加班单小时1,审核加班小时1,加班类别2,加班单小时2,审核加班小时2,休假类别,休假小时,进5,出5,进6,出6,进7,出7,进8,出8,进9,出9,进10,出10

  2. 2015/03/24,星期二,06:50,11:47,12:24,17:36, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  3. 2015/03/25,星期三,06:52,11:38,12:14,17:29, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  4. 2015/03/26,星期四,07:04,11:36,11:55,17:17, , , , ,E&E早班, , , ,8.00,平日加班,2.50,2.50, , , , , , , , , , , , , , , , , 

  5. 2015/03/27,星期五,06:57,11:28,12:04,17:27, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  6. 2015/03/28,星期六, , , , , , , , ,REST, , , , , , , , , , , , , , , , , , , , , , , , 

  7. 2015/03/29,星期日, , , , , , , , ,REST, , , , , , , , , , , , , , , , , , , , , , , , 

  8. 2015/03/30,星期一, , , , , , , , ,E&E早班, , , , , , , , , , ,固定年假换休假,8.00, , , , , , , , , , , , 

  9. 2015/03/31,星期二, , , , , , , , ,E&E早班, , , , , , , , , , ,事假,8.00, , , , , , , , , , , , 

  10. 2015/04/01,星期三,06:50,11:35,12:06,17:28, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  11. 2015/04/02,星期四,07:03,11:40,12:15,17:31, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  12. 2015/04/03,星期五,06:55,11:38,12:13,17:34, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  13. 2015/04/04,星期六,06:47,11:15,11:48,18:30, , , , ,REST, , , , ,公休日加班,12.00,12.00, , , , , , , , , , , , , , , , , 

  14. 2015/04/05,星期日, , , , , , , , ,REST, , , , , , , , , , , , , , , , , , , , , , , , 

  15. 2015/04/06,星期一, , , , , , , , ,REST, , , , , , , , , , , , , , , , , , , , , , , , 

  16. 2015/04/07,星期二, , , , , , , , ,E&E早班, , , , , , , , , , ,固定年假,8.00, , , , , , , , , , , , 

  17. 2015/04/08,星期三,06:59,07:37,07:45,11:40,12:14,17:33, , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  18. 2015/04/09,星期四,07:02,11:30,12:01,13:13,14:51,17:16, , ,E&E早班, , , ,8.00,平日加班,3.00,2.50, , , , , , , , , , , , , , , , , 

  19. 2015/04/10,星期五,07:03,11:34,11:58,17:28, , , , ,E&E早班, , , ,8.00,平日加班,3.00,3.00, , , , , , , , , , , , , , , , , 

  20. 2015/04/11,星期六,07:13,11:26,11:50,18:32, , , , ,REST, , , , ,公休日加班,12.00,12.00, , , , , , , , , , , , , , , , , 

  21. 2015/04/12,星期日, , , , , , , , ,REST, , , , , , , , , , , , , , , , , , , , , , , , 

  22. 2015/04/13,星期一,06:53,11:38,12:11,14:02,14:23,17:34, , ,E&E早班, , , ,8.00, , , , , , , , , , , , , , , , , , , , 

  23. 2015/04/14,星期二,07:05,11:34,12:03,17:28, , , , ,E&E早班, , , ,8.00, , , , , , , , , , , , , , , , , , , , 

  24. 2015/04/15,星期三,07:01,11:36,12:05,17:33, , , , ,E&E早班, , , ,8.00, , , , , , , , , , , , , , , , , , , , 

  25. 2015/04/16,星期四,07:04,11:33,12:05,17:29, , , , ,E&E早班, , , ,8.00, , , , , , , , , , , , , , , , , , , , 

  26. 2015/04/17,星期五,07:04, , , , , , , ,E&E早班,8.00, , , , , , , , , , , , , , , , , , , , , , , 

  27.  , , , , , , , , , , ,8.00, , ,112.00, ,53.50,53.00, , , , ,24.00, , , , , , , , , , , , 
复制代码
作者: boyangcoco    时间: 2015-4-19 17:10

回复 2# yu2n


        谢谢老师!
        我并不是想用 VBS 代码去计算, 考勤系统会自动计算这些数据,我只想通过这种方法练习如何抓取网页内需要的信息.
        通过昨天至今天的努力,已基本解决了数据提取的问题.
        我是用了很笨的方法去提取这些数据的, 不知道有没有更好的方法更准确的获取这些数据?
        基本上是靠下面两句一个一个的去抓取的........
  1. f.WriteLine("CHname=ie.document.getElementById(""_ctl10"").value")

  2. RomText12 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(12),""</td>"")(0),""&nbsp;"",""0"")
复制代码
作者: boyangcoco    时间: 2015-4-19 17:12

这是我自己写的代码:
也不知道是否如有不妥或者可以优化的地方;
已经实现了当前需求功能.
还望老师们赐教;
  1. Dim US,PW

  2. US=Inputbox("Please input your user name:","Input User Name","",0,0) 

  3. if IsEmpty(US) then

  4. wscript.quit

  5. else

  6. PW=Inputbox ("Please input your Password:","Input Password","",0,0)

  7. if IsEmpty(PW) then

  8. else

  9. MyVar = MsgBox ("Please confirm if need delete itself by program:"&vbCrlf&"If answer is yes, Please click button <Yes> "&vbCrlf&"If answer is no, Please click button <No>.", 65, "Function of delete itself")

  10. if MyVar=1 then

  11. Call CDelFile

  12. else

  13. Call CFile

  14. end if

  15. end if

  16. end if

  17. Dim WshShell  

  18. Set WshShell=WScript.CreateObject("WScript.Shell")  

  19. WshShell.Run "D:\Automatic.Query.Attendance.Data.vbs"

  20. 

  21. Sub CFile

  22. Dim fso, f

  23. set fso = CreateObject("Scripting.FileSystemObject")

  24. set f = fso.CreateTextFile("D:\Automatic.Query.Attendance.Data.vbs",true)

  25. f.WriteLine("Set ie = CreateObject(""InternetExplorer.Application"") ")

  26. f.WriteLine("ie.navigate ""http://huahr02/essusergz/""")

  27. f.WriteLine("While ie.busy Or ie.readystate <> 4")

  28. f.WriteLine("Wend ")

  29. f.WriteLine("ie.document.getElementById(""txtUserID"").value = """&US&"""")

  30. f.WriteLine("ie.document.getElementById(""txtPassword"").value = """&PW&"""")

  31. f.WriteLine("ie.document.getElementById(""btnLogon"").click")

  32. f.WriteLine("While ie.busy Or ie.readystate <> 4")

  33. f.WriteLine("Wend ")

  34. f.WriteLine("ie.navigate ""http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx""")

  35. f.WriteLine("While ie.busy Or ie.readystate <> 4")

  36. f.WriteLine("Wend ")

  37. f.WriteLine("Startdate=ie.document.getElementById(""dbxBegin:Cal_txtLeft"").value")

  38. f.WriteLine("Enddate=ie.document.getElementById(""dbxEnd:Cal_txtLeft"").value")

  39. f.WriteLine("SAPnum=ie.document.getElementById(""_ctl4"").value")

  40. f.WriteLine("Num=ie.document.getElementById(""_ctl7"").value")

  41. f.WriteLine("CHname=ie.document.getElementById(""_ctl10"").value")

  42. f.WriteLine("ENname=ie.document.getElementById(""_ctl13"").value")

  43. f.WriteLine("DPname=ie.document.getElementById(""_ctl16"").value")

  44. f.WriteLine("WCname=ie.document.getElementById(""_ctl19"").value")

  45. f.WriteLine("CCname=ie.document.getElementById(""_ctl22"").value")

  46. f.WriteLine("Joindate=ie.document.getElementById(""_ctl26"").value")

  47. f.WriteLine("Leavedate=ie.document.getElementById(""_ctl29"").value")

  48. f.WriteLine("Worknum=ie.document.getElementById(""_ctl32"").value")

  49. f.WriteLine("MidWorknum=ie.document.getElementById(""_ctl35"").value")

  50. f.WriteLine("NightWorknum=ie.document.getElementById(""_ctl38"").value")

  51. f.WriteLine("TOT=ie.document.getElementById(""_ctl41"").value")

  52. f.WriteLine("Dim strText")

  53. f.WriteLine("with CreateObject(""MSXml2.xmlhttp"")")

  54. f.WriteLine(".Open ""GET"",""C:\Users\Harris\Desktop\Attendence Detail.htm"",false")

  55. f.WriteLine(".Send")

  56. f.WriteLine("strText = ByteToStr(.Responsebody)")

  57. f.WriteLine("end with")

  58. f.WriteLine("Function ByteToStr(ByVal arrByte)")

  59. f.WriteLine("With CreateObject(""Adodb.Stream"")")

  60. f.WriteLine(".Type = 1")

  61. f.WriteLine(".Open")

  62. f.WriteLine(".Write arrByte")

  63. f.WriteLine(".Position = 0")

  64. f.WriteLine(".Type = 2")

  65. f.WriteLine(".Charset = ""GB2312""")

  66. f.WriteLine("ByteToStr = .Readtext")

  67. f.WriteLine(".Close")

  68. f.WriteLine("End With")

  69. f.WriteLine("End Function")

  70. f.WriteLine("RomText12 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(12),""</td>"")(0),""&nbsp;"",""0"")")

  71. f.WriteLine("RomText13 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(13),""</td>"")(0),""&nbsp;"",""0"")")

  72. f.WriteLine("RomText14 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(14),""</td>"")(0),""&nbsp;"",""0"")")

  73. f.WriteLine("RomText15 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(15),""</td>"")(0),""&nbsp;"",""0"")")

  74. f.WriteLine("RomText17 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(17),""</td>"")(0),""&nbsp;"",""0"")")

  75. f.WriteLine("RomText18 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(18),""</td>"")(0),""&nbsp;"",""0"")")

  76. f.WriteLine("RomText23 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(23),""</td>"")(0),""&nbsp;"",""0"")")

  77. f.WriteLine("msgbox ""Chinese Name:    ""&CHname&vbCrlf&""English Name:      ""&ENname&vbCrlf&""SAP Number:       ""&SAPnum&vbCrlf&""Employee Name: ""&num&vbCrlf&""Department:    ""&DPname&vbCrlf&""Work Cell:           ""&WCname&vbCrlf&""Cost Center:       ""&CCname&vbCrlf&""-----------------------------------------------""&vbCrlf&""Join Date:     ""&Joindate&vbCrlf&""Leave Date:   ""&Leavedate&vbCrlf&""Start Date:   ""&Startdate&vbCrlf&""End Date:     ""&Enddate&vbCrlf&""-----------------------------------------------""&vbCrlf&""Work Days:    ""&Worknum&"" Days""&vbCrlf&""B Shifts Days: ""&MidWorknum&"" Days""&vbCrlf&""C Shifts Days: ""&NightWorknum&"" Days""&vbCrlf&""-----------------------------------------------""&vbCrlf&""OT Upper Limit: ""&TOT&"" Hours""&vbCrlf&""-----------------------------------------------""&vbCrlf&""Absenteeism: ""&RomText12&"" h""&vbCrlf&""Late: ""&RomText13&"" h""&vbCrlf&""Leave Early: ""&RomText14&"" h""&vbCrlf&""Actual working hours: ""&RomText15&"" h""&vbCrlf&""Apply Over Time: ""&RomText17&"" h""&vbCrlf&""Actual Over Time: ""&RomText18&"" h""&vbCrlf&""Vacation Hours: ""&RomText23&"" h"",0,""Query results: Make By HB Huang""")

  78. f.WriteLine("wscript.quit")

  79. f.Close()

  80. set f = nothing

  81. set fso = nothing

  82. End Sub

  83. 

  84. Sub CDelFile

  85. Dim fso, f

  86. set fso = CreateObject("Scripting.FileSystemObject")

  87. set f = fso.CreateTextFile("D:\Automatic.Query.Attendance.Data.vbs",true)

  88. f.WriteLine("Set ie = CreateObject(""InternetExplorer.Application"") ")

  89. f.WriteLine("ie.navigate ""http://huahr02/essusergz/""")

  90. f.WriteLine("While ie.busy Or ie.readystate <> 4")

  91. f.WriteLine("Wend ")

  92. f.WriteLine("ie.document.getElementById(""txtUserID"").value = """&US&"""")

  93. f.WriteLine("ie.document.getElementById(""txtPassword"").value = """&PW&"""")

  94. f.WriteLine("ie.document.getElementById(""btnLogon"").click")

  95. f.WriteLine("While ie.busy Or ie.readystate <> 4")

  96. f.WriteLine("Wend ")

  97. f.WriteLine("ie.navigate ""http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx""")

  98. f.WriteLine("While ie.busy Or ie.readystate <> 4")

  99. f.WriteLine("Wend ")

  100. f.WriteLine("Startdate=ie.document.getElementById(""dbxBegin:Cal_txtLeft"").value")

  101. f.WriteLine("Enddate=ie.document.getElementById(""dbxEnd:Cal_txtLeft"").value")

  102. f.WriteLine("SAPnum=ie.document.getElementById(""_ctl4"").value")

  103. f.WriteLine("Num=ie.document.getElementById(""_ctl7"").value")

  104. f.WriteLine("CHname=ie.document.getElementById(""_ctl10"").value")

  105. f.WriteLine("ENname=ie.document.getElementById(""_ctl13"").value")

  106. f.WriteLine("DPname=ie.document.getElementById(""_ctl16"").value")

  107. f.WriteLine("WCname=ie.document.getElementById(""_ctl19"").value")

  108. f.WriteLine("CCname=ie.document.getElementById(""_ctl22"").value")

  109. f.WriteLine("Joindate=ie.document.getElementById(""_ctl26"").value")

  110. f.WriteLine("Leavedate=ie.document.getElementById(""_ctl29"").value")

  111. f.WriteLine("Worknum=ie.document.getElementById(""_ctl32"").value")

  112. f.WriteLine("MidWorknum=ie.document.getElementById(""_ctl35"").value")

  113. f.WriteLine("NightWorknum=ie.document.getElementById(""_ctl38"").value")

  114. f.WriteLine("TOT=ie.document.getElementById(""_ctl41"").value")

  115. f.WriteLine("Dim strText")

  116. f.WriteLine("with CreateObject(""MSXml2.xmlhttp"")")

  117. f.WriteLine(".Open ""GET"",""C:\Users\Harris\Desktop\Attendence Detail.htm"",false")

  118. f.WriteLine(".Send")

  119. f.WriteLine("strText = ByteToStr(.Responsebody)")

  120. f.WriteLine("end with")

  121. f.WriteLine("Function ByteToStr(ByVal arrByte)")

  122. f.WriteLine("With CreateObject(""Adodb.Stream"")")

  123. f.WriteLine(".Type = 1")

  124. f.WriteLine(".Open")

  125. f.WriteLine(".Write arrByte")

  126. f.WriteLine(".Position = 0")

  127. f.WriteLine(".Type = 2")

  128. f.WriteLine(".Charset = ""GB2312""")

  129. f.WriteLine("ByteToStr = .Readtext")

  130. f.WriteLine(".Close")

  131. f.WriteLine("End With")

  132. f.WriteLine("End Function")

  133. f.WriteLine("RomText12 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(12),""</td>"")(0),""&nbsp;"",""0"")")

  134. f.WriteLine("RomText13 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(13),""</td>"")(0),""&nbsp;"",""0"")")

  135. f.WriteLine("RomText14 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(14),""</td>"")(0),""&nbsp;"",""0"")")

  136. f.WriteLine("RomText15 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(15),""</td>"")(0),""&nbsp;"",""0"")")

  137. f.WriteLine("RomText17 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(17),""</td>"")(0),""&nbsp;"",""0"")")

  138. f.WriteLine("RomText18 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(18),""</td>"")(0),""&nbsp;"",""0"")")

  139. f.WriteLine("RomText23 = Replace(Split(Split(strText,""<td class=""""nbtext"""" style=""""COLOR:white"""" width=""""70px"""">"")(23),""</td>"")(0),""&nbsp;"",""0"")")

  140. f.WriteLine("msgbox ""Chinese Name:    ""&CHname&vbCrlf&""English Name:      ""&ENname&vbCrlf&""SAP Number:       ""&SAPnum&vbCrlf&""Employee Name: ""&num&vbCrlf&""Department:    ""&DPname&vbCrlf&""Work Cell:           ""&WCname&vbCrlf&""Cost Center:       ""&CCname&vbCrlf&""-----------------------------------------------""&vbCrlf&""Join Date:     ""&Joindate&vbCrlf&""Leave Date:   ""&Leavedate&vbCrlf&""Start Date:   ""&Startdate&vbCrlf&""End Date:     ""&Enddate&vbCrlf&""-----------------------------------------------""&vbCrlf&""Work Days:    ""&Worknum&"" Days""&vbCrlf&""B Shifts Days: ""&MidWorknum&"" Days""&vbCrlf&""C Shifts Days: ""&NightWorknum&"" Days""&vbCrlf&""-----------------------------------------------""&vbCrlf&""OT Upper Limit: ""&TOT&"" Hours""&vbCrlf&""-----------------------------------------------""&vbCrlf&""Absenteeism: ""&RomText12&"" h""&vbCrlf&""Late: ""&RomText13&"" h""&vbCrlf&""Leave Early: ""&RomText14&"" h""&vbCrlf&""Actual working hours: ""&RomText15&"" h""&vbCrlf&""Apply Over Time: ""&RomText17&"" h""&vbCrlf&""Actual Over Time: ""&RomText18&"" h""&vbCrlf&""Vacation Hours: ""&RomText23&"" h"",0,""Query results: Make By HB Huang""")

  141. f.WriteLine("set copy1=createobject(""scripting.filesystemobject"")")

  142. f.WriteLine("copy1.getfile(wscript.scriptfullname).delete")

  143. f.WriteLine("wscript.quit")

  144. f.Close()

  145. set f = nothing

  146. set fso = nothing

  147. End Sub
复制代码
作者: boyangcoco    时间: 2015-4-20 09:33

回复 2# yu2n

公司的考勤系统是需要登录的,
     登录页面: http://huahr02/essusergz/
     需要获取数据的页面: http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx
     这种情况应该如何去实现呢?????

    刚才在公司试了一下昨天我发的代码, 前面的可以实现, 但是后面的无法取值.

     RomText12 = Replace(Split(Split(strText1,"<td class=""nbtext"" style=""COLOR:white"" width=""70px"">")(1),"</td>")(0),"&nbsp;","0")

    老师,如果我只想获取最后一行数据, 应该用什么语句实现呢? 并且要一个一个的输出.
    比如:   
      实际工作时间: 112.00
      加班单小时1:   53.50
      审核加班小时1: 53.00
      休假小时: 24.00
作者: yu2n    时间: 2015-4-20 16:05

本帖最后由 yu2n 于 2015-4-20 16:08 编辑

如果一个复杂的表达式可以拆分来写，尽量拆分。这样比较容易排错，代码也清晰易懂。

假设有一多行字符串 s ，需要获取最后一行，可如下处理：
  1. s = "a" & vbCrLf & "b" & vbCr & "C" & vbLf & "D"

  2. s = Replace(s, vbCr, vbLf)    ' 统一换行符

  3. s = Replace(s, vbLf, vbLf)

  4. arr = Split(s, vbLf)           ' 以换行符vbLf分割，将字符串转为数组

  5. s = arr(UBound(arr))           ' 获取数组最后一个元素

  6. MsgBox s
复制代码
作者: boyangcoco    时间: 2015-4-20 20:40

回复 6# yu2n 现在的问题是,公司的考勤系统需要登录,
而我需要获取的数据是登录后的页面(附件里的Html)
登录页面: http://huahr02/essusergz/
登录后的页面:  http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx(附件里的Html)
如何获取数据??
我只要能显示出以下结果就行......
重点是学会思路...........
作者: yu2n    时间: 2015-4-20 23:13

登录的话，如果自动化代价太高，还是手工登录吧。参考 12306 验证码，不要花太多力气在这里。

思路的话，随便扯扯。

1. 登录方式未知，某些登录方式自动化代价太高，需要手工登录。
基础验证，安全性最低，但是很多组件都支持带账户验证，编程比较方便。
非基础验证...
带验证码...手工登录的话，下个步骤建议直接用 InternetExplorer.Application 来实现 BS 交互获取内容。

2. 分析通信方式、数据结构，确定使用哪个组件来获取登录后的网页内容。
InternetExplorer.Application？WinHTTP？Msxml2.ServerXMLHTTP？

3.  验证数据。

...
作者: boyangcoco    时间: 2015-4-21 13:02

回复 8# yu2n

    你好，用“InternetExplorer.Application”   其实我用现在的方法已经成功登陆并获取了一些有“name”属性值的数据。   但是我不会用现在的方法或取其它没有“name”属性的对象的值，
   所以请大神帮帮忙的.........

登陆并获取一些数据的部分代码：

  1. Set ie = CreateObject("InternetExplorer.Application") 

  2. ie.navigate "http://huahr02/essusergz/"

  3. While ie.busy Or ie.readystate <> 4

  4. Wend 

  5. ie.document.getElementById("txtUserID").value = Username

  6. ie.document.getElementById("txtPassword").value = Password 

  7. ie.document.getElementById("btnLogon").click

  8. While ie.busy Or ie.readystate <> 4

  9. Wend 

  10. ie.navigate "http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx" ’等前面页面加载完成后，输入此网址（即可得到需要获取数据的页面）

  11. While ie.busy Or ie.readystate <> 4

  12. Wend 

  13. Startdate=ie.document.getElementById("dbxBegin:Cal_txtLeft").value
复制代码
‘获取一个“name”属性为“dbxBegin:Cal_txtLeft”的值。



目前可以“登陆并获取部分数据”的源代码（还可以在运行后Copy自身至D盘。）：

  1. set copy1=createobject("scripting.filesystemobject")          

  2. copy1.getfile(wscript.scriptfullname).copy("d:\Automatic Query Attendance Data(EN).vbs")

  3. 

  4. Username="UserName" 'Replace to your user name

  5. Password="PassWord" 'replace to your password

  6. 

  7. if username = "UserName" or PassWord = "PassWord" then

  8.      msgbox "UserName: "&UserName&vbcrlf&"PassWord: "&PassWord&vbcrlf,4096,"Pleae check your UserName and Password: "

  9. 	 wscript.quit

  10.      else

  11.      Set ie = CreateObject("InternetExplorer.Application") 

  12. 		ie.navigate "http://huahr02/essusergz/"

  13. 		While ie.busy Or ie.readystate <> 4

  14. 		Wend 

  15. 		ie.document.getElementById("txtUserID").value = Username

  16. 		ie.document.getElementById("txtPassword").value = Password 

  17. 		ie.document.getElementById("btnLogon").click

  18. 		While ie.busy Or ie.readystate <> 4

  19. 		Wend 

  20. 		ie.navigate "http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx"

  21. 		While ie.busy Or ie.readystate <> 4

  22. 		Wend 

  23. 		Startdate=ie.document.getElementById("dbxBegin:Cal_txtLeft").value

  24. 		Enddate=ie.document.getElementById("dbxEnd:Cal_txtLeft").value

  25. 		SAPnum=ie.document.getElementById("_ctl4").value

  26. 		Num=ie.document.getElementById("_ctl7").value

  27. 		CHname=ie.document.getElementById("_ctl10").value

  28. 		ENname=ie.document.getElementById("_ctl13").value

  29. 		DPname=ie.document.getElementById("_ctl16").value

  30. 		WCname=ie.document.getElementById("_ctl19").value

  31. 		CCname=ie.document.getElementById("_ctl22").value

  32. 		Joindate=ie.document.getElementById("_ctl26").value

  33. 		Leavedate=ie.document.getElementById("_ctl29").value

  34. 		Worknum=ie.document.getElementById("_ctl32").value

  35. 		MidWorknum=ie.document.getElementById("_ctl35").value

  36. 		NightWorknum=ie.document.getElementById("_ctl38").value

  37. 		TOT=ie.document.getElementById("_ctl41").value

  38. 		msgbox "Chinese Name:    "&CHname&vbCrlf&"English Name:      "&ENname&vbCrlf&"SAP Number:       "&SAPnum&vbCrlf&"Employee Name: "&num&vbCrlf&"Department:    "&DPname&vbCrlf&"Work Cell:           "&WCname&vbCrlf&"Cost Center:       "&CCname&vbCrlf&"-----------------------------------------------"&vbCrlf&"Join Date:     "&Joindate&vbCrlf&"Leave Date:   "&Leavedate&vbCrlf&"Start Date:   "&Startdate&vbCrlf&"End Date:     "&Enddate&vbCrlf&"-----------------------------------------------"&vbCrlf&"Work Days:    "&Worknum&" Days"&vbCrlf&"B Shifts Days: "&MidWorknum&" Days"&vbCrlf&"C Shifts Days: "&NightWorknum&" Days"&vbCrlf&"-----------------------------------------------"&vbCrlf&"OT Upper Limit: "&TOT&" Hours"&vbCrlf&"-----------------------------------------------"&vbCrlf,0,"Query results: Make By HB Huang"

  39. 		msgbox "The wscript file exist in your computer disk D, the program will automatically help  you open the disk D!",VbOnlyOK,"Congratulations!"

  40. 		ie.navigate "D:\"

  41. 		wscript.quit

  42. end if
复制代码
作者: yu2n    时间: 2015-4-21 19:12

本帖最后由 yu2n 于 2015-4-21 19:13 编辑

回复 9# boyangcoco

参考：
HTML DOM 访问
http://www.w3school.com.cn/htmldom/dom_using.asp

VBS 示例：
  1. 'document.getElementById 示例：获取 ID 为 XX_ID 的网页内容

  2. ie.document.getElementById("XX_ID").innerHTML

  3. 

  4. 'document.getElementsByName 示例：获取 NAME 为 XX_NAME 的网页内容

  5. ie.document.getElementsByName("XX_NAME").innerHTML

  6. 

  7. 'document.getElementsByTagName 示例：遍历所有 div 节点，查找 class 为 yu2n 的节点

  8. For Each o In  ie.document.getElementsByTagName("div")

  9.   If o.className = "yu2n" Then

  10.     Msgbox o.parentNode.innerHTML

  11.     Exit For

  12.   End If

  13. Next

  14. 

  15. 'document.all 示例：遍历所有节点，查找 class 为 yu2n 的节点

  16. For Each o In ie.document.all

  17.   If o.className = "yu2n" Then

  18.     Msgbox o.parentNode.innerHTML

  19.     Exit For

  20.   End If

  21. Next
复制代码
  1. '获取 dom 节点后，定位该节点相关的 dom 节点

  2. o.childNodes  '得到s的全部子节点

  3. o.parentNode   '得到s的父节点

  4. o.nextSbiling   '获得s的下一个兄弟节点

  5. o.previousSbiling  '得到s的上一个兄弟节点

  6. o.firstChild   '获得s的第一个子节点

  7. o.lastChile   '获得s的最后一个子节点
复制代码
作者: boyangcoco    时间: 2015-4-21 23:20

回复 10# yu2n


    谢谢老师, 根据你的提示,我的代码部分已经完成了, 明天去公司测试下.......
    我的 QQ:284264152  可以加你QQ吗?
作者: boyangcoco    时间: 2015-4-22 08:04

代码测试出现一点点小问题, 不过现在已经解决了.
谢谢 yu2n 的帮助. Thank you very much!

经过测试,得出一小结论,
用
  1. tblDetail=ie.document.getElementById("tblDetail").innerHTML
复制代码
语句得到的源码跟用 IE 浏览器
直接看到的源码是不一样的. 怪不得之前一直查找不到正确的数据.
  1. Dim ie

  2. Set ie = CreateObject("InternetExplorer.Application")

  3. ie.navigate "http://huahr02/essusergz/"

  4. While ie.busy Or ie.readystate <> 4

  5. Wend 

  6. ie.document.getElementById("txtUserID").value = "用户名"

  7. ie.document.getElementById("txtPassword").value = "密码"

  8. ie.document.getElementById("btnLogon").click

  9. While ie.busy Or ie.readystate <> 4

  10. Wend 

  11. ie.navigate "http://huahr02/essusergz/webpages/attendanceinfo_jabil_c.aspx"

  12. While ie.busy Or ie.readystate <> 4

  13. Wend 

  14. '===================================================================================

  15. Startdate=ie.document.getElementById("dbxBegin:Cal_txtLeft").value

  16. Enddate=ie.document.getElementById("dbxEnd:Cal_txtLeft").value

  17. SAPNum=ie.document.getElementById("_ctl4").value

  18. MNum=ie.document.getElementById("_ctl7").value

  19. CHname=ie.document.getElementById("_ctl10").value

  20. ENname=ie.document.getElementById("_ctl13").value

  21. DepartmentName=ie.document.getElementById("_ctl16").value

  22. WorkcellName=ie.document.getElementById("_ctl19").value

  23. CostCenter=ie.document.getElementById("_ctl22").value

  24. Joindate=ie.document.getElementById("_ctl26").value

  25. Leavedate=ie.document.getElementById("_ctl29").value

  26. WorkDays=ie.document.getElementById("_ctl32").value

  27. MidShiftNum=ie.document.getElementById("_ctl35").value

  28. NightShiftNum=ie.document.getElementById("_ctl38").value

  29. TOTUpperLimit=ie.document.getElementById("_ctl41").value

  30. '===================================================================================

  31. tblDetail=ie.document.getElementById("tblDetail").innerHTML

  32. RomText12 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(12),"</TD>")(0),"&nbsp;","0")

  33. RomText13 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(13),"</TD>")(0),"&nbsp;","0")

  34. RomText14 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(14),"</TD>")(0),"&nbsp;","0")

  35. RomText15 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(15),"</TD>")(0),"&nbsp;","0")

  36. RomText17 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(17),"</TD>")(0),"&nbsp;","0")

  37. RomText18 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(18),"</TD>")(0),"&nbsp;","0")

  38. RomText23 = Replace(Split(Split(tblDetail,"<TD style=""COLOR: white"" class=nbtext width=70>")(23),"</TD>")(0),"&nbsp;","0")

  39. msgbox "Chinese Name:     "&CHName&vbCrlf&"English Name:       "&ENName&vbCrlf&"SAP Number:        "&SAPNum&vbCrlf&"Employee Name:   "&MNum&vbCrlf&"Department:          "&DepartmentName&vbCrlf&"Work Cell:              "&WorkcellName&vbCrlf&"Cost Center:          "&CostCenter&vbCrlf&"-----------------------------------------------"&vbCrlf&"Join Date:      "&JoinDate&vbCrlf&"Start Date:    "&StartDate&vbCrlf&"End Date:      "&EndDate&vbCrlf&"-----------------------------------------------"&vbCrlf&"Work Days:      "&WorkDays&" Days"&vbCrlf&"B Shifts Days:   "&MidShiftNum&" Days"&vbCrlf&"C Shifts Days:   "&NightShiftNum&" Days"&vbCrlf&"-----------------------------------------------"&vbCrlf&"OT Upper Limit:   "&TOTUpperLimit&" Hours"&vbCrlf&"-----------------------------------------------"&vbCrlf&"Absenteeism:    "&RomText12&" h"&vbCrlf&"Late:                 "&RomText13&" h"&vbCrlf&"Leave Early:     "&RomText14&" h"&vbCrlf&"Actual working hours:   "&RomText15&" h"&vbCrlf&"Apply Over Time:          "&RomText17&" h"&vbCrlf&"Actual Over Time:         "&RomText18&" h"&vbCrlf&"Vacation Hours:            "&RomText23&" h",0,"Query results: Make By HB Huang"

  40. set ie=nothing

  41. wscript.quit
复制代码




欢迎光临 批处理之家 (http://bathome.net./) Powered by Discuz! 7.2