Hello Friends,
I have below macro which is set for the opening the XLSM (macros) file from the "Input" folder, and run the macros in that file.
Each file I have set the same macro name "Get Data" & "Time My Macro"
I need a solution for the two issues,
1. Can you provide any other log method better than below, I have separated code by line like below?
'/_________________________________________________________________________________
2. This is very important, currently due to Workbooks.Open function, each file in the input file open and run macro.
Then again next file open and run the set macros.
Is it possible that each file as I said in the Input folder can open in the background as per loop mentioned in code, and run the defined code?
(In short, instead of file open each time, it work silently either opening file in the background or without opening file, run the inside macros, possible?)
FYI Application.DisplayAlerts = False, this is not helping me...
Pls trigger my query by any advance method...
Thanks, RR
Option Explicit
Sub Run_All_Macros()
Application.ScreenUpdating = False
Dim CurrentWB As Workbook
Dim FileStr As String
Dim FileManager As Scripting.FileSystemObject
Set FileManager = New Scripting.FileSystemObject
Application.DisplayAlerts = False
'/__________________________________________________________________________________
'/ Log Method, this code enter the date of macros run and avoiding re-run again for the same day.
If Cells(Rows.Count, 2).End(xlUp).Row > 1 Then
If CDate(Format(Cells(Rows.Count, 2).End(xlUp).Value, "dd-mmm-yyyy")) = Date And LCase(Cells(Rows.Count, 2).End(xlUp).Offset(0, 1).Value) = "yes" Then
MsgBox ("Macros have been already run for " & Format(Date, "dd-mmm-yyyy"))
Exit Sub
Else
If CDate(Cells(Rows.Count, 2).End(xlUp).Value) = Date Then
Cells(Rows.Count, 2).End(xlUp).Offset(0, 1).Value = "Yes"
Else
Cells(Rows.Count, 2).End(xlUp).Offset(1, 1).Value = "Yes"
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Format(Date, "dd-mmm-yyyy")
End If
End If
Else
Cells(2, 3).Value = "Yes"
Cells(2, 2).Value = Format(Date, "dd-mmm-yyyy")
End If
'/______________________________________________________________________________
'/ Check if any file not mentioned under input folder
If Not FileManager.FolderExists(ActiveWorkbook.Path & "\Input") Then
MsgBox ("No Folder by the name of 'Input' exists")
Exit Sub
End If
FileStr = VBA.Dir(ActiveWorkbook.Path & "\Input\*.xlsm")
Do While Len(FileStr) > 0
If FileStr Like "Get Profit Values_*" Or FileStr Like "Loss Record*" Or FileStr Like "SaveAs_*" Then
'do nothing
Else
Set CurrentWB = Workbooks.Open(ActiveWorkbook.Path & "\Input\" & FileStr)
'If you want to add more code to change the opened workbook, you can add that here:
'Specifically calling the macro inside the workbook
Application.Run ("'" & FileStr & "'!Get_Data")
Application.Run ("'" & FileStr & "'!Time_My_Macro")
CurrentWB.Save
CurrentWB.Close
End If
FileStr = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Save the workbook, then close it
ThisWorkbook.Close savechanges:=True
End Sub
Bookmarks