excel-vba
Traverser toutes les feuilles dans Active Workbook
Recherche…
Récupérer tous les noms de feuilles de calcul dans Active Workbook
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
Boucler toutes les feuilles de tous les fichiers d'un dossier
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
Sous licence CC BY-SA 3.0
Non affilié à Stack Overflow