Excel VBA(八)遍历工作表文件WorkBook、页Sheet和单元格Cells

使用VBA对当前文件所在目录下的所有Excel文件中的所有Sheet中的所有单元格进行遍历的演示程序。

Sub FindFileCuurentFold_Date()
Dim MyPath, MyName, WbN As String
Dim i, j As Integer
Dim Num As Long
Dim Box As String
Dim rng As Range, ws As Worksheet, wb As Workbook
Application.ScreenUpdating = False
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.xlsx")
AWbName = ActiveWorkbook.Name
Num = 0
Sheets("文件列表").Cells(1, 1) = "文件"
Do While MyName <> ""
 If MyName <> AWbName Then
  Num = Num + 1
  Sheets(1).Cells(Num + 1, 1) = MyName
  'open
  Set wb = GetObject(MyPath & "\" & MyName)
  'MsgBox wb.Sheets(1).Cells(1, 1)
  For Each sht In wb.Sheets
    'MsgBox sht.Name
    For i = 1 To 100
      For j = 1 To 3
        If sht.Cells(i, j) = "日期" Then
         MsgBox sht.Cells(i, j)
        End If
      Next
    Next
  Next
  wb.Close
 End If
 MyName = Dir
Loop
Application.ScreenUpdating = True
MsgBox "共找到了" & Num & "个工作薄下的全部工作表。"
End Sub

 

发表评论