楼主
0Mouse |
Q：如何运用VBA提取辽宁快乐12网页的开奖结果数据到Excel工作表中呢？
辽宁快乐12网页链接：
- http://kl12.icaile.com/lnkl12.php?op=dcjb&num=100
预期效果：
A：代码如下：
- Sub 运用VBA提取辽宁快乐12网页的开奖结果数据到Excel工作表中()
- Application.ScreenUpdating = False
- Dim sTxt$, Rst, Ori, Astd, i%, j%, Arr, sAddr$
- With CreateObject("Microsoft.XMLHTTP")
- .Open "get", "http://kl12.icaile.com/lnkl12.php?op=dcjb&num=100", False
- .send
- sTxt = .responsetext '网页源文件
- End With
- Rows("3:" & Rows.Count).Clear
- Ori = Split(Split(sTxt, "chart-hr")(0), "chart-bg-qh")
- ReDim Rst(1 To UBound(Ori), 1 To 23)
- Astd = Split("EMDE,IMDI,MMDM,QMDQ,UMDU,YMDY,cMDc,gMDg,kMDk,AMTA,EMTE,IMTI", ",") '开奖号码对应的编码
- For i = 1 To UBound(Ori)
- '期号
- Rst(i, 1) = Mid(Split(Ori(i), "</td>")(0), 3)
- '开奖号码及其分布
- Arr = Split(Ori(i), "=M")
- For j = 0 To 4
- Rst(i, j + 2) = Format(Application.Match(Right(Arr(j), 4), Astd, 0), "00")
- Rst(i, Val(Rst(i, j + 2)) + 6) = Rst(i, j + 2)
- Next j
- Erase Arr
- '和值、跨度、大小比、奇偶比、质合比
- Arr = Split(Ori(i), "<td>")
- For j = 0 To 4
- Rst(i, UBound(Rst, 2) - j) = Split(Arr(UBound(Arr) - j), "<")(0)
- Next j
- Erase Arr
- Next i
- Union(Range("B3:R" & i + 1), Range("U3:W" & i + 1)).NumberFormatLocal = "@"
- With Range("A3").Resize(i - 1, UBound(Rst, 2))
- .Value = Rst
- .Font.Size = 9
- .HorizontalAlignment = xlCenter
- End With
- Union(Range("A3:A" & i + 1), Range("F3:F" & i + 1), Range("R3:R" & i + 1)).Borders(xlEdgeRight).LineStyle = xlContinuous
- Columns.AutoFit
- With Range("B3").Resize(i - 1, 17).Font
- .Bold = True
- .Color = vbRed
- End With
- Range("G3").Resize(i - 1, 12).Font.Color = vbBlue
- For j = 1 To i - 1
- sAddr = sAddr & "," & Cells(j + 2, Rst(j, 2) + 6).Address(0, 0)
- If Len(sAddr) > 230 Then
- Range(Mid(sAddr, 2)).Font.Color = vbRed
- sAddr = ""
- End If
- Next j
- If sAddr <> "" Then
- Range(Mid(sAddr, 2)).Font.Color = vbRed
- sAddr = ""
- End If
- Application.ErrorCheckingOptions.NumberAsText = False
- Range("A3").Select
- Erase Rst: Erase Ori: Erase Astd
- Application.ScreenUpdating = True
- End Sub
附件：
 运用VBA提取辽宁快乐12网页的开奖结果数据到Excel工作表中.rar
来自群组: |