Auto open each files in folder and perform operations

[chasoe]

Dear Sirs,

I've found a macro that can open each files in the folder and I modified it as below. The codes cannot run to the end but only just open one file and stop.
However, if I open the explorer and press F5 to run the codes, it can run to the end successfully.

Sub AutoImportY()
'
'Hotkey : Ctrl + Shft + Y
'
    Dim fsoFile As Object
    Dim myPath  As String

    Dim counter As Long
     
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
      
    myPath = ActiveWorkbook.Path & "\"
   
    For Each fsoFile In CreateObject("Scripting.FileSystemObject").GetFolder(myPath).Files
    
       If LCase(fsoFile.Name) Like "*flex*.xl*" Then         'quoted like file should be lower case
           
            counter = counter + 1
            Workbooks.Open (myPath & fsoFile.Name)
              
       Run ("'zMacro-Fleks (add Cost Summary sheet).xlsm'!AutoCostSummary")
       ActiveWorkbook.Close SaveChanges:=True
           
       End If
     
    Next
    
    Application.ScreenUpdating = True
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
      
    MsgBox counter & " Flex files added Cost Summary.", , "Done"

End Sub

Would be grateful if someone could give me some guidance.

Thanks in advance.

 

[NdNoviceHlp]

You are creating a file system object with each loop. Trial this (untested)...

Sub AutoImportY()
'
'Hotkey : Ctrl + Shft + Y
'
    Dim fsoFile As Object
    Dim myPath  As String

    Dim counter As Long, FSO As Object
     
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
      
    myPath = ActiveWorkbook.Path & "\"
   
   Set FSO = CreateObject("scripting.filesystemobject")

    For Each fsoFile In FSO.GetFolder(myPath).Files
    
       If LCase(fsoFile.Name) Like "*flex*.xl*" Then         'quoted like file should be lower case
           
            counter = counter + 1
            Workbooks.Open (myPath & fsoFile.Name)
              
       Run ("'zMacro-Fleks (add Cost Summary sheet).xlsm'!AutoCostSummary")
       ActiveWorkbook.Close SaveChanges:=True
           
       End If
     
    Next
    
    Application.ScreenUpdating = True
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
     Set FSO = Nothing
    MsgBox counter & " Flex files added Cost Summary.", , "Done"

End Sub

HTH. Dave

 

[chasoe]

Dear Dave,

Your revised codes make no difference.

The same thing happens. That is if I stay in Excel and press hotkey Ctrl + Shft+Y, it can only open one of the files the folder and will not proceed and seems to have finished the codes (and no error prompted). But if I go to VBA Explorer and press F5, then all the files in the folder will be processed.

Any idea ? As I have about 200 files in the folder to be processed for year end budgeting purpose, the codes can help a lot.

 

[NdNoviceHlp]

This part usually has the folder name..

myPath = ActiveWorkbook.Path & "\YourFolderName"

This might be wrong depending on your file names...

If LCase(fsoFile.Name) Like "*flex*.xl*" Then

Other than that I can't suggest anything. HTH. Dave

 

[chasoe]

On numerous search in the internet, I got an answer now.
There is a bug if using shortcut key with "shift" : if I change to Ctrl + y then the codes can finish to the end