Skip specific files in folder with vba loop

mjohnston0209

Board Regular
Joined
Nov 6, 2017
Messages
55
Hello,

I have a macro that opens all xlsm files in a folder and runs a macro housed within that file. This worked perfectly because there were three xlsx files that I wanted to skip. However, I had to convert those xlsx files to xlsm. Is there a way to still skip those files by identifying the file name? Below is my current code. Any help would be greatly appreciated!



VBA Code:
Sub Update_All_WIPS()
'PURPOSE: To loop through all xlsm WIP files and perform the DataUpdate macro

Dim wb As Workbook
Dim myPath As String
Dim MyFile As String
Dim myExtension As String

Dim WIPName As String
Dim myDataPath As String
Dim MyDataFile As String


'Optimize Macro Speed
  Application.ScreenUpdating = False

'Identify File paths
  myPath = "W:\Accounting\Financial Reporting\WIPS\"
  myDataPath = "W:\Accounting\Financial Reporting\Data Source\"

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsm*"

'Target Path with Ending Extention
  MyFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While MyFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & MyFile)
   
    'Open Data Source File
      WIPName = Sheets("Project Summary").Range("B2") & " SQL Data Source.xlsx"
      MyDataFile = myDataPath & WIPName
      Workbooks.Open MyDataFile
   
    'Select WIP File
      wb.Activate
   
    'Change First Worksheet's Background Fill Blue
      Application.Run "'" & wb.Name & "'!DataUpdate"
   
    'Close Data Source File
      Workbooks(WIPName).Close SaveChanges:=False
   
    'Save and Close Workbook
      wb.Close SaveChanges:=True

    'Get next file name
      MyFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.ScreenUpdating = True

End Sub
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You could try something like this, obviously replacing "Book1.xlsm", "Book2.xlsm", "Book3.xlsm" with the real filenames.
VBA Code:
Sub Update_All_WIPS()
'PURPOSE: To loop through all xlsm WIP files and perform the DataUpdate macro

Dim wb As Workbook
Dim myPath As String
Dim MyFile As String
Dim myExtension As String

Dim WIPName As String
Dim myDataPath As String
Dim MyDataFile As String
    
    
    'Optimize Macro Speed
    Application.ScreenUpdating = False
    
    'Identify File paths
    myPath = "W:\Accounting\Financial Reporting\WIPS\"
    myDataPath = "W:\Accounting\Financial Reporting\Data Source\"
    
    'Target File Extension (must include wildcard "*")
    myExtension = "*.xlsm*"
    
    'Target Path with Ending Extention
    MyFile = Dir(myPath & myExtension)
    
    'Loop through each Excel file in folder
    Do While MyFile <> ""
    
        Select Case MyFile
            Case "Book1.xlsm", "Book2.xlsm", "Book3.xlsm"
                ' do nothing
            Else
                ' do stuff
                'Set variable equal to opened workbook
                Set wb = Workbooks.Open(Filename:=myPath & MyFile)
                
                'Open Data Source File
                WIPName = Sheets("Project Summary").Range("B2") & " SQL Data Source.xlsx"
                MyDataFile = myDataPath & WIPName
                Workbooks.Open MyDataFile
                
                'Select WIP File
                wb.Activate
                
                'Change First Worksheet's Background Fill Blue
                Application.Run "'" & wb.Name & "'!DataUpdate"
                
                'Close Data Source File
                Workbooks(WIPName).Close SaveChanges:=False
                
                'Save and Close Workbook
                wb.Close SaveChanges:=True
        End Select
        
        'Get next file name
        MyFile = Dir
    Loop
    
    'Message Box when tasks are completed
    MsgBox "Task Complete!"
    
ResetSettings:
    'Reset Macro Optimization Settings
    Application.ScreenUpdating = True

End Sub[/vba]
 
Upvote 0
Solution
You could try something like this, obviously replacing "Book1.xlsm", "Book2.xlsm", "Book3.xlsm" with the real filenames.
VBA Code:
Sub Update_All_WIPS()
'PURPOSE: To loop through all xlsm WIP files and perform the DataUpdate macro

Dim wb As Workbook
Dim myPath As String
Dim MyFile As String
Dim myExtension As String

Dim WIPName As String
Dim myDataPath As String
Dim MyDataFile As String
   
   
    'Optimize Macro Speed
    Application.ScreenUpdating = False
   
    'Identify File paths
    myPath = "W:\Accounting\Financial Reporting\WIPS\"
    myDataPath = "W:\Accounting\Financial Reporting\Data Source\"
   
    'Target File Extension (must include wildcard "*")
    myExtension = "*.xlsm*"
   
    'Target Path with Ending Extention
    MyFile = Dir(myPath & myExtension)
   
    'Loop through each Excel file in folder
    Do While MyFile <> ""
   
        Select Case MyFile
            Case "Book1.xlsm", "Book2.xlsm", "Book3.xlsm"
                ' do nothing
            Else
                ' do stuff
                'Set variable equal to opened workbook
                Set wb = Workbooks.Open(Filename:=myPath & MyFile)
               
                'Open Data Source File
                WIPName = Sheets("Project Summary").Range("B2") & " SQL Data Source.xlsx"
                MyDataFile = myDataPath & WIPName
                Workbooks.Open MyDataFile
               
                'Select WIP File
                wb.Activate
               
                'Change First Worksheet's Background Fill Blue
                Application.Run "'" & wb.Name & "'!DataUpdate"
               
                'Close Data Source File
                Workbooks(WIPName).Close SaveChanges:=False
               
                'Save and Close Workbook
                wb.Close SaveChanges:=True
        End Select
       
        'Get next file name
        MyFile = Dir
    Loop
   
    'Message Box when tasks are completed
    MsgBox "Task Complete!"
   
ResetSettings:
    'Reset Macro Optimization Settings
    Application.ScreenUpdating = True

End Sub[/vba]
Norie, thanks for the help! I had to make two quick changes, but now the macro works perfectly! I wrote "Case Is =" and "Case Else". Note that the quotes aren't included in the actual macro.

Thanks again!
 
Upvote 0
Oops, I really should double check code before I post it.:eek:
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top