Loop through files in directory

[dilshod_k]

Hi there,

I've got 13 files in directory (SMA (1).xlsm to SMA (12).xlsm plus "Data File.xlsm". I need to write code that opens each file (except one called "Data File.xlsm") runs "macro 3" in each file (same name in each file) and after that closes each file after saving changes. I have written the code and could not debug one of the lines. Would anyone help me please to sort this problem out? Thanks in advance.

Sub MasterKey()
'
' MasterKey Macro


Dim MyFolder As String
Dim Wb As Workbook






Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"


For Each Wb In MyFolder


If Wb.Name <> "Data File.xlsm" Then


Wb.Activate
Application.Run _
"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"




ActiveWorkbook.Close True
SaveChanges = True


End If


Next Wb




End Sub

 

[ask2tsp]

see the code below. Read all the comments carefully
you may have to setup a link to the Microsoft Scripting Runtime library
To create a reference of Microsoft Scripting Runtime Library follow the below steps: (from: www.exceltrick.com)

• First of all open the VBA editor by pressing “Alt + F11”.

• Next, navigate to “Tools” > “Reference” as shown above.

• This will open a references window. Here select and check the entry “Microsoft Scripting Runtime” and click “OK”.

Option Explicit

Sub MasterKey()
    Dim MyFolder As String
    Dim wb As Workbook
    '==== MyFolder is a string not an object, you cannot 'set' this =========
    'Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    '====================================================================
    
    MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    
    '==== for each «object» in «collection» does not work with MyFolder As String ===
    '==== to iterate through all files in a folder do something like
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    For Each wb In fso.GetFolder(MyFolder).Files
        '=======================
        'For Each Wb In MyFolder
        If wb.Name <> "Data File.xlsm" Then
            
            '==== if you put macro3 in this workbook you can run it by ====
            macro3 wb 'why not give it a more descriptive name?
            '=======================
            
            'Wb.Activate
            'Application.Run _
            '"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"
            '.ActiveWorkbook.Close True
            'SaveChanges = True
            
            wb.Close SaveChanges:=True  'note the ':=' syntax used with named arguments
        End If
    Next wb
End Sub

Sub macro3(wb As Workbook)
    'put the code of the macro from one of the .xlsm files here
    'you probably have to alter it a bit
End Sub

 

[dilshod_k]

Thanks a lot ask2tsp! Appreciate it. I'll try to do it. The reason I did not give Macro3 more descriptive name is simply because it consists
of another 3 Macros with descriptive names and all them perform unrelated to each other functions. I'm new in this business and can't do proper coding, so I have to record macros and then tweak them manually to my needs.

Thanks again and have a nice day.

see the code below. Read all the comments carefully
you may have to setup a link to the Microsoft Scripting Runtime library
To create a reference of Microsoft Scripting Runtime Library follow the below steps: (from: www.exceltrick.com)

• First of all open the VBA editor by pressing “Alt + F11”.

• Next, navigate to “Tools” > “Reference” as shown above.

• This will open a references window. Here select and check the entry “Microsoft Scripting Runtime” and click “OK”.

Option Explicit

Sub MasterKey()
    Dim MyFolder As String
    Dim wb As Workbook
    '==== MyFolder is a string not an object, you cannot 'set' this =========
    'Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    '====================================================================
    
    MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    
    '==== for each «object» in «collection» does not work with MyFolder As String ===
    '==== to iterate through all files in a folder do something like
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    For Each wb In fso.GetFolder(MyFolder).Files
        '=======================
        'For Each Wb In MyFolder
        If wb.Name <> "Data File.xlsm" Then
            
            '==== if you put macro3 in this workbook you can run it by ====
            macro3 wb 'why not give it a more descriptive name?
            '=======================
            
            'Wb.Activate
            'Application.Run _
            '"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"
            '.ActiveWorkbook.Close True
            'SaveChanges = True
            
            wb.Close SaveChanges:=True  'note the ':=' syntax used with named arguments
        End If
    Next wb
End Sub

Sub macro3(wb As Workbook)
    'put the code of the macro from one of the .xlsm files here
    'you probably have to alter it a bit
End Sub

 

[dilshod_k]

Hello ask2tsp,

Thanks again for your advice. Today I tried to follow your instructions. The only change I made to the code is file path. After few steps it gives error message: Run-time error '13' - Type mismatch.
I would be grateful is you could help me to sort out the problem.
Here is details as it follows:

Sub MasterKey()
Dim MyFolder As String
Dim wb As Workbook
'==== MyFolder is a string not an object, you cannot 'set' this =========
'Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
'====================================================================

MyFolder = "C:\Users\User\Downloads\Trading\Test\Master DataFile"


'==== for each «object» in «collection» does not work with MyFolder As String ===
'==== to iterate through all files in a folder do something like
Dim fso As FileSystemObject
Set fso = New FileSystemObject
For Each wb In fso.GetFolder(MyFolder).Files ------ ERROR MESSAGE HERE!!! - Run-time error '13' - Type mismatch.
'=======================
'For Each Wb In MyFolder
If wb.Name <> "Data File.xlsm" Then

'==== if you put macro3 in this workbook you can run it by ====
macro3 wb 'why not give it a more descriptive name?
'=======================

'Wb.Activate
'Application.Run _
'"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"
'.ActiveWorkbook.Close True
'SaveChanges = True

wb.Close SaveChanges:=True 'note the ':=' syntax used with named arguments
End If
Next wb
End Sub


Sub macro3(wb As Workbook)
'put the code of the macro from one of the .xlsm files here
'you probably have to alter it a bit
End Sub

 

[ask2tsp]

I corrected the error. Try this version.

Option Explicit

Sub MasterKey()
    Dim MyFolder    As String
    Dim wbFile      As File
    
    Dim wb As Workbook
    '==== MyFolder is a string not an object, you cannot 'set' this =========
    'Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    '====================================================================
    
    MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    
    '==== for each «object» in «collection» does not work with MyFolder As String ===
    '==== to iterate through all files in a folder do something like
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    For Each wbFile In fso.GetFolder(MyFolder).Files
        '=======================
        'For Each Wb In MyFolder
        If wbFile.Name <> "Data File.xlsm" Then
        
            Set wb = Workbooks.Open(wbFile, , True) 'open readonly
            
            '==== if you put macro3 in this workbook you can run it by ====
            macro3 wb 'why not give it a more descriptive name?
            '=======================
            
            'Wb.Activate
            'Application.Run _
            '"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"
            '.ActiveWorkbook.Close True
            'SaveChanges = True
            
            wb.Close SaveChanges:=True  'note the ':=' syntax used with named arguments
        End If
    Next wbFile
End Sub

Sub macro3(wb As Workbook)
    'put the code of the macro from one of the .xlsm files here
    'you probably have to alter it a bit
End Sub

 

[dilshod_k]

I corrected the error. Try this version.

Option Explicit

Sub MasterKey()
    Dim MyFolder    As String
    Dim wbFile      As File
    
    Dim wb As Workbook
    '==== MyFolder is a string not an object, you cannot 'set' this =========
    'Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    '====================================================================
    
    MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
    
    '==== for each «object» in «collection» does not work with MyFolder As String ===
    '==== to iterate through all files in a folder do something like
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    For Each wbFile In fso.GetFolder(MyFolder).Files
        '=======================
        'For Each Wb In MyFolder
        If wbFile.Name <> "Data File.xlsm" Then
        
            Set wb = Workbooks.Open(wbFile, , True) 'open readonly
            
            '==== if you put macro3 in this workbook you can run it by ====
            macro3 wb 'why not give it a more descriptive name?
            '=======================
            
            'Wb.Activate
            'Application.Run _
            '"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"
            '.ActiveWorkbook.Close True
            'SaveChanges = True
            
            wb.Close SaveChanges:=True  'note the ':=' syntax used with named arguments
        End If
    Next wbFile
End Sub

Sub macro3(wb As Workbook)
    'put the code of the macro from one of the .xlsm files here
    'you probably have to alter it a bit
End Sub

Thanks a lot for help.
But for some reason it is still not working. This time error message at the very beginning of the code.:



Sub MasterKey()
Dim MyFolder As String
Dim wbFile As File -------- ERROR MESSAGE HERE Compile error: User-defined type not defined.
Dim wb As Workbook

'==== MyFolder is a string not an object, you cannot 'set' this =========
'Set MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"
'====================================================================

MyFolder = "C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data"

'==== for each «object» in «collection» does not work with MyFolder As String ===
'==== to iterate through all files in a folder do something like
Dim fso As FileSystemObject
Set fso = New FileSystemObject
For Each wbFile In fso.GetFolder(MyFolder).Files
'=======================
'For Each Wb In MyFolder
If wbFile.Name <> "Data File.xlsm" Then

Set wb = Workbooks.Open(wbFile, , True) 'open readonly

'==== if you put macro3 in this workbook you can run it by ====
macro3 wb 'why not give it a more descriptive name?
'=======================

'Wb.Activate
'Application.Run _
'"'C:\Users\Admin\Downloads\Excel Price Downloader\Daily Data\ *.xlsm!Macro3()"
'.ActiveWorkbook.Close True
'SaveChanges = True

wb.Close SaveChanges:=True 'note the ':=' syntax used with named arguments
End If
Next wbFile
End Sub


Sub macro3(wb As Workbook)
'put the code of the macro from one of the .xlsm files here
'you probably have to alter it a bit
End Sub

 

[dilshod_k]

Sorry, I did not realise that Microsoft Scripting Runtime was not activated, so please disregard previous message about error message at the very beginning of the code.