Solved: MsgBox - Error

[parttime_guy]

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

[GTO]

Hi there,

Well... I was experiencing some "vision problems" yesterday, but hopefully not missing anything here. I do not see anything to stop progress in the above, so would wonder if there's anything in Do_something() or some2() that causes an early end to the running code.

Mark

[Paul_Hossler]

Just wondering if you're closing the macro workbook?


if ThisWorkbook.name <> mybook.name then
mybook.Close savechanges:=True
endif



Didn't test it

Paul

[mdmackillop]

or maybe you want to exclude the active book from the processing

For Fnum = LBound(MyFiles) To UBound(MyFiles)
If Not MyFiles(Fnum) = ActiveWorkbook.Name Then

[parttime_guy]

Hi Guz,

I searched for another Macro which is below - and does the same functions.
And..... I am still cannot solve the MsgBox Prompt

Sub allfolderfiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String

MyPath = ThisWorkbook.path
ChDir MyPath
TheFile = Dir("*.xl*")
Do While TheFile <> ""
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
'============Start your Code here

'Call Do_something

'============End your Code here
wb.Close savechanges:=True
TheFile = Dir
Loop
MsgBox ("Macro Successfully Completed")
End Sub


I even commented the Call functions - as GTO suggested - the sub runs all all files in the folder - but does not give the MsgBox prompt once the loop is finished.

Any ideas......

Plz help

BR

[mdmackillop]

As Paul stated, you are closing the book containing the code. Try stepping through your macro to see how this happens.

[mbarron]

If you exclude the initial workbook as mdmackillop had suggested, the MsgBox the macro will complete.

Sub allfolderfiles()
Dim wb As Workbook
Dim TheFile As String
Dim MyPath As String
Dim myFile As String
myFile = ActiveWorkbook.Name
MyPath = ThisWorkbook.Path
ChDir MyPath
TheFile = Dir("*.xl*")
Do While TheFile <> ""
If TheFile <> myFile Then
Set wb = Workbooks.Open(MyPath & "\" & TheFile)
'============Start your Code here

'Call Do_something

'============End your Code here
wb.Close savechanges:=True
End If
TheFile = Dir
Loop
MsgBox ("Macro Successfully Completed")
End Sub

[parttime_guy]

Hi MBarron,

Thxs for your code - it has solved my MsgBox problem.

BR