楼主
xmyjk |
Q:如何使用xmlhttp与数组xmlhttp与数组导入错位的XML数据?
A:- Option Explicit
- Sub test()
- Dim tmp() As String, i As Long, n As Long, JC As Worksheet, FILE_PATH As String
- Dim J As Integer
-
- On Error Resume Next
- If Sheets("机构评级汇总") Is Nothing Then
- Set JC = Sheets.Add(AFTER:=Worksheets("起始页"))
- JC.Name = "机构评级汇总"
- Else
- Set JC = Sheets("机构评级汇总")
- Worksheets("机构评级汇总").Activate
- Cells.Select
- Selection.ClearContents
- End If
-
- [a1:k1] = Split("代码,评级日期,机构数,最新评级,上月机构数,上月评级,调整幅度,2010A,2011E,2012E,2013E", ",")
- FILE_PATH = ThisWorkbook.Path & "\" & "机构评级汇总.xml"
-
- Application.ScreenUpdating = False
- Open FILE_PATH For Input As #1
- tmp() = Split(Split(Split(StrConv(InputB(LOF(1), 1), vbUnicode), "<lines>")(1), "</lines>")(0), "</line>")
- Close #1
-
- For i = 0 To UBound(tmp)
- n = [a65536].End(xlUp).Row + 1
- Cells(n, 1) = Split(Split(tmp(i), "<stk>")(1), "</stk>")(0)
- For J = 3 To 12
- Cells(n, J - 1) = Split(Split(Split(tmp(i), "<dat col=""" & J & """")(1), "</dat>")(0), ">")(1)
- Next
- Next
-
- Erase tmp
-
- [a:k].Columns.AutoFit
- Application.ScreenUpdating = True
- MsgBox "Ok"
- End Sub
 决策数据.rar
|