Loop Through Folder but it NEVER STOPS

[Realtreegirl75]

I have done all the digging I can online to try to find out how to make this work but no matter what I do, this loop never stops! I end up having to ESC out of the sub. The goal is to loop through a folder (the same folder, so I could set a file path pretty easily, if that would help!) and then:
open the first file
unhide a sheet
save changes
close the workbook
move on to the next
But then exit the sub when its done so its not an endless loop!

Can anyone help with this?? I have a dozen ways I could really use this code but at the moment its cause more trouble than its worth!

Sub LoopDIRWorkbooks()
   Dim MyFolder As String
   Dim MyFile As String
   Dim wbk As Workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
   If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
msgbox "You did not select a folder"
      Exit Sub
   End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = DIR(MyFolder) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
Do While MyFile <> ""
   'Opens the file and assigns to the wbk variable for future use
   Set wbk = Workbooks.Open(fileName:=MyFolder & MyFile)

      ActiveWorkbook.Unprotect 
     ActiveSheet.Unprotect
      For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "Dashboard" Then
        Sheet.Visible = True
    End If
Next Sheet
    ActiveSheet.Protect 
    ActiveWorkbook.Protect 


wbk.Close savechanges:=True
MyFile = DIR 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
End Sub

 

[rollis13]

Tested your macro, it does it's job and ends when there are no other files to process.
Do you have in your folder file with AutoOpen or WorkBook_Open macro's that could create confusion once opened ?

 

[Realtreegirl75]

How do I check the AutoOpen? I doubt I have any macros that is creating confusion as everything for this macro is in the same spot (no calling to a different location that may have been overlooked).

 

[rollis13]

You need to know what macro's are in the file you are processing.
Have you done your debugging ? Go to the "VBE" pane, if the "Immediate" pane isn't visible use (CTRL+G), add this line of code Debug.Print MyFile just before
Set wbk = Workbooks.Open(fileName:=MyFolder & MyFile) then place the cursor on this second line and press F9 to insert a BreakPoint on it.
Now launch your macro with F5 and keep your eyes on the names added in the "Immediate" pane every time you press F5 once again. You should see only the names in the folder in process and after the last one the macro will end. If the files are many, to make it less time consuming, create a new dummy folder with only a couple of files in it.

 

[Realtreegirl75]

Unfortunately, it just kept looping. At this point, I stopped the macro myself. There were only 3 files in this folder. Any other ideas?

That being said, I didn't know about the Debug.Print Myfile, so that was awesome to learn!

 

[rollis13]

Tested once again your macro but wasn't able to replicate your issue. The only difference is that my test file don't contain macros but yours are .xlsm; what sort of macro do they contain ?
Maybe a 'WorkBook_Open' in the ThisWorkBook module ora an 'AutoOpen' in one of the used modules ? If so, what does it do ?

 

[Realtreegirl75]

Found it! This is the only Workbook_Open code I have in the workbooks. I had an issue with them changing to manual calculation so I added this to make sure they didn't do that:

Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic
End Sub

 

[rollis13]

I added your macro to my test file and that doesn't create your problem either. Sorry, but at the moment I have no other idea, your macro keeps working perfectly.
How do you launch your macro "LoopDIRWorkbooks" ? manually (or Button) or is there a chance that it is Called by some other macro ?

 

[Realtreegirl75]

I usually run it manually from the Visual Basics window. And it's not called for another macro, I run this just as it shows. Totally baffled!

 

[rollis13]

I give up, another chance only if you attach your file (without sensible data). By the way, maybe, have you activated some sort of Add-In in your Excel ?