VBA算得上是Excel的顶级应用技术了，使用VBA几乎可以解决99.99%的Excel应用问题，本问题也不例外。通过VBA遍历同一路径下的所有文档，查找文档中的的特定内容，并把对应值提取出来。具体图文演示如下：
Sub VBA应用()
Dim mypath As String, file As String
Dim wb As Workbook
Dim rng As Range
mypath = ThisWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
file = Dir(mypath & “*.xlsx”)
Do While Len(file) > 0
If file <> ThisWorkbook.Name Then
Workbooks.Open mypath & Application.PathSeparator & file, False
Set wb = ActiveWorkbook
Set rng = wb.Sheets(1).UsedRange.Find(“测试”).Offset(0, 1)
If rng Is Nothing Then
Exit Do
Else
n = n + 1
With ThisWorkbook.Sheets(1)
.Cells(n + 1, 2) = rng.Value
.Cells(n + 1, 3) = Left(file, Len(file) – 5)
End With
End If
file = Dir
wb.Close
End If
Loop
Set wb = Nothing
Application.ScreenUpdating =True
Application.DisplayAlerts = True
End Sub
代码放入测试工作薄VBE编辑界面的模块中，按F5运行即可提取到各文档中测试对应的值。欢迎关注@Excel泥瓦匠，Excel学习，E路有你！
本文来自投稿，不代表天一生活立场，如若转载，请注明出处：http://tiyigo.com/it/21687.html
