|
目的:读取网易数据直接到excel表格里(不用下载CSV文件)
由于自己对网上读取数据知识一点都不懂,代码也是照着EH大侠代码来修改,现只能改到t1 = BytesToBstr(.ResponseBody, "GB2312")为止,后面代码不知怎么修改.
请那位老师帮忙修改代码,怎么可以直接读取到excel里头,而不用先下载CSV文件,谢谢了
代码如下
Sub 读取网易数据()
Dim winhttp, URL, i, j, t1, k, d, code, c, oDoc, n, r, y1, m, f, arr1, h, jd1, y2, jd2, s, arr2, pages, pages2, ccode, p
Set winhttp = CreateObject("WinHttp.WinHttpRequest.5.1")
Set oDoc = CreateObject("htmlfile")
On Error Resume Next
With winhttp
URL = "由于没有权限发URL,请看附件"
.Open "GET", URL, False
.setRequestHeader "Connection", "Keep-Alive"
.send
t1 = BytesToBstr(.ResponseBody, "GB2312")
With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.SetText t1
.PutInClipboard
End With
arr1 = Split(t1, "{symbol")
ReDim arr(1 To UBound(arr1), 1 To 10)
For i = 1 To UBound(arr1)
For j = 1 To 10
arr(i, j) = Split(Split(arr1(i), """,")(j - 1), ":""")(1)
Next
Next
Range("a" & [a65536].End(xlUp).Row + 1).Resize(UBound(arr1), 12) = arr
Erase arr
End With
End Sub
Function BytesToBstr(strBody, CodeBase)
Dim objStream
On Error Resume Next
Set objStream = CreateObject("Adodb.Stream")
With objStream
.Type = 1
.Mode = 3
.Open
.Write strBody
.Position = 0
.Type = 2
.Charset = CodeBase
BytesToBstr = .ReadText
End With
objStream.Close
Set objStream = Nothing
If Err.Number <> 0 Then BytesToBstr = ""
On Error GoTo 0
End Function
|
|