Skip specific files in folder with vba loop

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
36
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:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
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]
 
Solution

mjohnston0209

New Member
Joined
Nov 6, 2017
Messages
36
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!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Oops, I really should double check code before I post it.:eek:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,126
Messages
5,570,332
Members
412,319
Latest member
akshat1231
Top