parttime_guy
04-14-2010, 11:36 PM
Dear All,
No problem with the Macro.
But.... I can't seem to get the MsgBox ("Macro Successfully Completed") prompt after the macro completes the full loop.
Sub Process_All_in_folder()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Application.DisplayAlerts = False
'Fill in the path\folder where the files are
MyPath = ThisWorkbook.path & "\"
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xl*")
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
'=================Start your Code Here
Call Do_something
Call some2
'=================End your Code Here
mybook.Close savechanges:=True
Next Fnum
End If
Application.DisplayAlerts = True
MsgBox "Macro Successfully Completed"
End Sub
Kindly help....
Thx-n-BR
No problem with the Macro.
But.... I can't seem to get the MsgBox ("Macro Successfully Completed") prompt after the macro completes the full loop.
Sub Process_All_in_folder()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String, Fnum As Long
Dim mybook As Workbook
Application.DisplayAlerts = False
'Fill in the path\folder where the files are
MyPath = ThisWorkbook.path & "\"
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xl*")
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
'=================Start your Code Here
Call Do_something
Call some2
'=================End your Code Here
mybook.Close savechanges:=True
Next Fnum
End If
Application.DisplayAlerts = True
MsgBox "Macro Successfully Completed"
End Sub
Kindly help....
Thx-n-BR