Looping over all sheets of all workbooks using scripting runtime

[tomas]

Hello I put in bold line which I dont know how to go on. fil is scripting file And I want to loop over all wsk (worksheets) in fil and I Though I might use fil.worksheets , but it doesn't work

THanks for looking at it

Sub vyber()

Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim folder As Scripting.folder
Dim folder1 As String
Dim wsk As Worksheet
Set fso = New Scripting.FileSystemObject
folder1 = "S:\KLIENTI\Provident Financial\TendrPro2016\Nabidky_IIkolo"
Set folder = fso.GetFolder(folder1)

For Each fil In folder.Files
    If fso.GetExtensionName(fil.Path) = "xlsx" And Left(fil.Name, 1) <> "~" Then
        Debug.Print fil.Name
      
       [BOLD] For Each wsk In fil.[/BOLD]
            Debug.Print wsk.Name
            If wsk.Name = "NOVA_G" Then
                Range(Cells(16, 1), Cells(28, 30)).Copy
                ActiveWorkbook("list1").Select
                ActiveSheet.Paste Destination:=Range("C100000").End(xlUp).Select
            End If
        Next wsk
    End If
Next fil

End Sub

 

[Marc L]

Hi !

No needs FileSystemObject, easier is to use Dir VBA function
as explained in its help & sample …

Your fil is a single object, so not a collection to use in a For Each loop !
​

 

[Debaser]

You can't access the worksheets of a workbook using the Scripting.Runtime. You need to open the workbook in Excel.