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!
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: