vba对xml文件解析.docx
- 文档编号:4640648
- 上传时间:2022-12-07
- 格式:DOCX
- 页数:14
- 大小:15.44KB
vba对xml文件解析.docx
《vba对xml文件解析.docx》由会员分享,可在线阅读,更多相关《vba对xml文件解析.docx(14页珍藏版)》请在冰豆网上搜索。
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,"
'delete
IfInStr(ItemCode,"")<>0Then
Worksheets("sheet1").Rows(theRow).delete
i=i-1
EndIf
IfInStr(ItemCode,"
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," Worksheets("Sheet1").Rows(theRow).delete i=i-1 EndIf Nexti EndSub
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- vba xml 文件 解析