使用VBA不打开其他文件取数,可以参考以下例子:根据Excel文件中的姓名,提取Word文件的证件号码。
代码如下
Sub Opiona()
‘//每个文档中都有这样一行(或称段):
‘职工姓名: 杨XX 性别: 男
‘身份证号码:XXX0402198604100XXX
‘禁止系统刷屏?触发其他事件等
‘On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
t = Timer
Set SH0 = Sheets(“12月份清单 “)
Dim wd, mypath$, wj$, i&, x%, zf$
Set wd = CreateObject(“word.application”)
mypath = ThisWorkbook.Path & “\”
wj = Dir(mypath & “*.doc”)
Do While wj <> “”
With wd.Documents.Open(mypath & wj)
x = .Paragraphs.Count
For i = 1 To x
zf = .Paragraphs(i).Range
If zf Like “职工姓名:*” Then
S = S + 1
STR职工姓名 = Trim(Mid(zf, InStr(zf, “职工姓名:”) + 5, InStr(zf, “性别:”) – 6))
zf2 = .Paragraphs(i + 1).Range
If zf2 Like “身份证号码:*” Then
INT身份证号码 = Trim(Split(zf2, “身份证号码:”)(1))
End If
Set C = SH0.Range(“C:C”).Find(STR职工姓名, , LOOKAT:=xlWhole)
If Not C Is Nothing Then
SH0.Cells(C.Row, 5) = INT身份证号码
End If
End If
Next
.Close False
End With
wj = Dir
Loop
wd.Quit
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox “一共用时:” & Format(Timer – t, “#0.0000″) & ” 秒”, , “XX提示!!”
End Sub