Modify this to only open files less than a certain size?

[TheWennerWoman]

Hello,

I have this which works but I need it modified to only open files less than 100kb in size:

Option Explicit

Sub OpenAllFiles()

    Dim filpath As String
    Dim fso As Scripting.FileSystemObject
    Dim fil As Scripting.File
    Dim fldr As Scripting.Folder

    filpath = "C:\Users\Spartacus\OneDrive - Acme Toys\Documents\Daily Control Files\"
    Set fso = New Scripting.FileSystemObject
    Set fldr = fso.GetFolder(filpath)
Application.DisplayAlerts = False

On Error Resume Next
For Each fil In fldr.Files
    With Application.Workbooks.Open(fil.Path)
        .RefreshAll
'        .Close False
    End With
Next fil
    

End Sub

Any help appreciated as always

 

[Micron]

Maybe like this

On Error Resume Next
For Each fil In fldr.Files
    Debug.Print "name: " & fil.Name & "  Size: " & fil.Size
    If fil.Size < 20480 And InStr(fil.Name, "xls") > 0 Then
        With Application.Workbooks.Open(fil.Path)
            .RefreshAll
'           .Close False
        End With
    End If
Next fil

The debug.print is only there to give you a better idea of the number returned by fil.Size (it is in bytes, not KB). Edit to suit and comment out or remove when not needed. Also, I assumed you wanted only certain file types to open so I chose anything with "xls" thus it would not open xlt. Note that if the middle of the file contains the letter combination you use, then that will open also. That may require modification - you might get away with fil.Type

 

[TheWennerWoman]

Thanks, I'll give it a go when I'm in the office.

The folder in question only has *.xlsm files in it so I am safe with that.

 

[TheWennerWoman]

Maybe like this

On Error Resume Next
For Each fil In fldr.Files
    Debug.Print "name: " & fil.Name & "  Size: " & fil.Size
    If fil.Size < 20480 And InStr(fil.Name, "xls") > 0 Then
        With Application.Workbooks.Open(fil.Path)
            .RefreshAll
'           .Close False
        End With
    End If
Next fil

The debug.print is only there to give you a better idea of the number returned by fil.Size (it is in bytes, not KB). Edit to suit and comment out or remove when not needed. Also, I assumed you wanted only certain file types to open so I chose anything with "xls" thus it would not open xlt. Note that if the middle of the file contains the letter combination you use, then that will open also. That may require modification - you might get away with fil.Type

works perfectly, many thanks!