excel-vba
Pętlę przez wszystkie arkusze w aktywnym skoroszycie
Szukaj…
Pobierz wszystkie nazwy arkuszy w aktywnym skoroszycie
Option Explicit
Sub LoopAllSheets()
Dim sht As Excel.Worksheet
' declare an array of type String without committing to maximum number of members
Dim sht_Name() As String
Dim i As Integer
' get the number of worksheets in Active Workbook , and put it as the maximum number of members in the array
ReDim sht_Name(1 To ActiveWorkbook.Worksheets.count)
i = 1
' loop through all worksheets in Active Workbook
For Each sht In ActiveWorkbook.Worksheets
sht_Name(i) = sht.Name ' get the name of each worksheet and save it in the array
i = i + 1
Next sht
End Sub
Pętla przez wszystkie arkusze we wszystkich plikach w folderze
Sub Theloopofloops()
Dim wbk As Workbook
Dim Filename As String
Dim path As String
Dim rCell As Range
Dim rRng As Range
Dim wsO As Worksheet
Dim sheet As Worksheet
path = "pathtofile(s)" & "\"
Filename = Dir(path & "*.xl??")
Set wsO = ThisWorkbook.Sheets("Sheet1") 'included in case you need to differentiate_
between workbooks i.e currently opened workbook vs workbook containing code
Do While Len(Filename) > 0
DoEvents
Set wbk = Workbooks.Open(path & Filename, True, True)
For Each sheet In ActiveWorkbook.Worksheets 'this needs to be adjusted for specifiying sheets. Repeat loop for each sheet so thats on a per sheet basis
Set rRng = sheet.Range("a1:a1000") 'OBV needs to be changed
For Each rCell In rRng.Cells
If rCell <> "" And rCell.Value <> vbNullString And rCell.Value <> 0 Then
'code that does stuff
End If
Next rCell
Next sheet
wbk.Close False
Filename = Dir
Loop
End Sub
Modified text is an extract of the original Stack Overflow Documentation
Licencjonowany na podstawie CC BY-SA 3.0
Nie związany z Stack Overflow