vba对xml文件解析.docx
《vba对xml文件解析.docx》由会员分享,可在线阅读,更多相关《vba对xml文件解析.docx(14页珍藏版)》请在冰豆网上搜索。
![vba对xml文件解析.docx](https://file1.bdocx.com/fileroot1/2022-12/7/383d5118-faa5-4966-9fa4-55c5102863c4/383d5118-faa5-4966-9fa4-55c5102863c41.gif)
vba对xml文件解析
Subdelete()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
'取报表日期
'TabDate=CDate(Left(Worksheets("Sheet1").Cells(2,4).Value,11))
'DateString=Format(TabDate,"yyyy-mm-dd")
'DateStringF=Format(TabDate,"yyyymmdd")
'OnErrorResumeNext
'
'OnErrorGoTo0
'="正在预备网调日报数据,请稍候..."
'FileName=
'DimMisFileNameAsString
'MisFileName=HDRB_MISPath&"福建"&DateStringF&".FD288"
'OpenFileNameForOutputAs#1
'tmpStr="D288"
'Print#1,tmpStr
'Print#1,""
'tmpStr="<发电负荷288点:
:
福建date='"&DateString&"'>"
'Print#1,tmpStr
'LineMaxCharNum
'ItemCode="@@数据名称"
'ItemVal="数据值"
'tmpStr=ItemCode&Space
(1)&ItemVal
'Print#1,tmpStr
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
'Worksheets("sheet1").Cells(theRow,theCol).Select
'Worksheets("Sheet1").Cells(field2(k),field3(k)).Value
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
'IfInStr(ItemCode,"
formula>")<>0OrInStr(ItemCode,"")<>0OrInStr(ItemCode,"")<>0Then
'delete
IfInStr(ItemCode,"")<>0Then
Worksheets("sheet1").Rows(theRow).delete
i=i-1
EndIf
IfInStr(ItemCode,"")<>0Then
Worksheets("sheet1").Rows(theRow).delete
i=i-1
EndIf
'解析公式
'IfInStr(ItemCode,">")Then
's_num=InStr(ItemCode,">")
'ItemCode=Mid(ItemCode,s_num+20)
'ItemCode=RTrim(ItemCode)
'lengthall=Len(ItemCode)
'ItemCode=Mid(ItemCode,1,lengthall-21)
'Worksheets("Sheet1").Cells(theRow,theCol).Value=ItemCode
'EndIf
'Print#1,tmpStr
Nexti
'文件终止
'tmpStr="发电负荷288点:
:
福建>"
'Print#1,tmpStr
'Close#1
EndSub
Subid()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
'解析id
IfInStr(ItemCode,"P:
")<>0Then
s_num=InStr(ItemCode,"P:
")
ItemCode=Mid(ItemCode,s_num+2)
ItemCode=RTrim(ItemCode)
lengthall=Len(ItemCode)
ItemCode=Mid(ItemCode,1,lengthall-2)
Worksheets("Sheet1").Cells(theRow,theCol).Value=ItemCode
EndIf
Nexti
EndSub
Subchinese()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
'解析中文
IfInStr(ItemCode,">")<>0Then
s_num=InStr(ItemCode,">")
ItemCode=Mid(ItemCode,s_num+17)
ItemCode=RTrim(ItemCode)
lengthall=Len(ItemCode)
IfInStr(ItemCode,"
>")<>0Then
ItemCode=Mid(ItemCode,1,lengthall-18)
EndIf
Worksheets("Sheet1").Cells(theRow,theCol).Value=ItemCode
EndIf
Nexti
EndSub
Subformular()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
'解析公式
IfInStr(ItemCode,">")Then
s_num=InStr(ItemCode,">")
ItemCode=Mid(ItemCode,s_num+20)
ItemCode=RTrim(ItemCode)
lengthall=Len(ItemCode)
ItemCode=Mid(ItemCode,1,lengthall-21)
Worksheets("Sheet1").Cells(theRow,theCol).Value=ItemCode
EndIf
Nexti
EndSub
Subzhuanzhi()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
Dimid_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
id_num=0
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
'解析公式
IfInStr(ItemCode,"
formula>")<>0Then
id_num=id_num+1
Worksheets("Sheet1").Cells(theRow+1,2).Value=id_num
Worksheets("Sheet1").Cells(theRow+1,3).Value=Worksheets("Sheet1").Cells(theRow+2,1).Value
Worksheets("Sheet1").Cells(theRow+2,1).Value="aaaaaa"
Worksheets("Sheet1").Cells(theRow+1,4).Value=Worksheets("Sheet1").Cells(theRow+3,1).Value
Worksheets("Sheet1").Cells(theRow+3,1).Value="bbbbbb"
Forj=1To50
IfInStr(Worksheets("Sheet1").Cells(theRow+3+j,1).Value,"
formula>")=0Then
Worksheets("Sheet1").Cells(theRow+1,4+j).Value=Worksheets("Sheet1").Cells(theRow+3+j,1).Value
Worksheets("Sheet1").Cells(theRow+3+j,1).Value="cccccc"
Else
ExitFor
EndIf
Nextj
EndIf
Nexti
EndSub
Subfinish()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
Dimid_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
id_num=0
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
'解析公式
IfInStr(ItemCode,"
formula>")<>0Then
Worksheets("Sheet1").Rows(theRow).delete
i=i-1
EndIf
IfInStr(ItemCode,"aaaaaa")<>0Then
Worksheets("Sheet1").Rows(theRow).delete
i=i-1
EndIf
IfInStr(ItemCode,"bbbbbb")<>0Then
Worksheets("Sheet1").Rows(theRow).delete
i=i-1
EndIf
IfInStr(ItemCode,"cccccc")<>0Then
Worksheets("Sheet1").Rows(theRow).delete
i=i-1
EndIf
Nexti
EndSub
Subdelete_new()
DimiAsLong
DimnSpaceAsLong
DimItemCodeAsString'代码
DimItemValAsString'数值
DimTabDateAsDate
DimtheRowCountAsLong'有效报表列
DimtheRowSAsLong'开始数据所在行
DimtheColSAsLong'开始数据所在列
DimtheRowAsLong'数据所在行
DimtheColAsLong'数据所在列
DimDataNumAsLong'数据个数
DimFileNameAsString,LineOutAsString
DimDateStringAsString
DimDateStringFAsString
DimtmpStrAsString
Dims_numAsInteger
Dimid_numAsInteger
dfile=""
theRowCount=65000
theRowS=1
theColS=1
id_num=0
Fori=0TotheRowCount
theRow=theRowS+i
theCol=theColS
ItemCode=Worksheets("Sheet1").Cells(theRow,theCol).Value
IfInStr(ItemCode,"")<>0Then
Worksheets("Sheet1").Rows(theRow).delete
i=i-1
EndIf
IfInStr(ItemCode,"")<>0Then
Worksheets("Sheet1").Rows(theRow).delete
i=i-1
EndIf
Nexti
EndSub