|
楼主把这么多工作薄中的工作表放在一个工作薄中再汇总,没必要,直接放在一个文件夹中就行了
还有一个受保护的视图问题没有解决,用代码打开时会出错
我也写了一个,只提取手机号:
Sub 手机号()
Dim thispath, thisname, myfile, myxls, r&
Call 去除受保护视图
thispath = ThisWorkbook.Path & "\专项附加扣除信息(导出)\"
thisname = ThisWorkbook.Name
myfile = Dir(thispath)
[a1] = "姓名"
[b1] = "身份证号"
[c1] = "手机号"
r = 1
Application.ErrorCheckingOptions.NumberAsText = False
Do While myfile <> ""
Set myxls = Workbooks.Open(thispath & myfile)
r = r + 1
Cells(r, "a") = myxls.Sheets(1).Range("b4")
Cells(r, "b") = myxls.Sheets(1).Range("c6")
Cells(r, "c") = myxls.Sheets(1).Range("g5")
myxls.Close False
myfile = Dir
Loop
r = Range("a65536").End(3).Row
Range("A1:C" & r).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
End Sub
Sub 去除受保护视图()
Dim v, objwmi
Const HKEY_CURRENT_USER = &H80000001
Set objwmi = GetObject("winmgmts:\\.\root\default:StdRegProv")
v = Application.Version
objwmi.CreateKey HKEY_CURRENT_USER, "Software\Microsoft\Office\" & v & "\Excel\Security\FileValidation"
objwmi.SetDWORDValue HKEY_CURRENT_USER, "Software\Microsoft\Office\" & v & "\Excel\Security\FileValidation", "EnableOnLoad", 0
End Sub |
|