vendredi 31 mai 2019

Use files in a folder - exceptions VBA

I need to perform certain task over some excel files located in subfolders (which are inside a folder). This task must exclude all those file's names that contain letter "v".

I used file name length since the files I want to use have that length (file names with letter "V" have more that 28 characters). Here is my code.

Sub test()
Application.ScreenUpdating = False

Dim Fso As Object, objFolder As Object, objSubFolder As Object
Dim FromPath As String
Dim FileInFolder As Object

FromPath = "C:\Prueba"
Set Fso = CreateObject("Scripting.filesystemobject")
Set objFolder = Fso.GetFolder(FromPath)

For Each objSubFolder In objFolder.Subfolders

    Dim wbk As Workbook
    Dim Filename As String, File As Variant
    Path = objSubFolder.Path
    Filename = Dir(Path & "\*.xlsx")

    For Each File In objSubFolder.Files
        If Len(Filename) = 28 Then 
            Do something
            wbk.Close True
            Filename = Dir
        End If
     Next File
Next objSubFolder

ActiveWorkbook.Close savechanges:=True
    Application.DisplayAlerts = True

Application.ScreenUpdating = True
End Sub

This code works OK until it reaches a file with "V". It just stuck in that file (do nothing, which is good) and then goes to the next subfolder, ignoring all the remaining excel files.

I need some advice how to deal with this.

thx

Aucun commentaire:

Enregistrer un commentaire