本帖最后由 pcl_test 于 2015-11-13 09:59 编辑
不好意思,没注意到需要把文件夹名的数字去掉,下面是更改之后的。- Dim regEx,oExcel,fso,fo,t1:t1=Time
- Set oExcel = CreateObject("Excel.Application")
- Set fso = CreateObject("scripting.filesystemobject")
- Set regEx = New RegExp:regEx.Pattern="[a-zA-Z0-9]*"
- Call mFolder(fso.GetFolder("e:\………………\分类内容"))
-
- Sub mFolder(p)
- Dim mName
- For Each i In p.Files
- if fso.GetExtensionName(i)="xlsx" Or fso.GetExtensionName(i)="xls" Then
- mName=regEx.Replace(i.ParentFolder.Name,"")
- Call ex(i.path,mName)
- End if
- next
- For Each mf In p.SubFolders
- Call mFolder(mf)
- Next
- End Sub
-
- Sub ex(p,na)
- Dim oWb,tmpV
- Set oWb = oExcel.Workbooks.Open(p)
- For i=1 To oWb.sheets.count
- For Each s In oWb.sheets(i).UsedRange
- tmpV=s.value
- If tmpV<>"" Then
- Select Case True
- Case CBool(InStr(tmpV,"广州市天河供电局")):s.value="广州越秀供电局"
- Case CBool(InStr(tmpV,"珠吉站新出F9调整东圃站F30负荷")):s.value=na
- Case CBool(InStr(tmpV,"082400WP20145213")):s.value="082000WP20157001"
- End Select
- End If
- Next
- Next
- oWb.Save:oWb.Close:Set oWb=Nothing
- End Sub
-
- oExcel.Quit
- Set oExcel=Nothing
- Set fso=Nothing
- Set regEx=Nothing
- msgbox "用时"&DateDiff("s",t1,Time)&"秒完成"
复制代码
|