CODEWITHSUNDEEP
excelvba
duckman
duckman

Reputation: 747

Stopping a file loop

I use the following code to loop through the files in the folders, open the file and then wait for data to load and save and stop. however, it keeps repeating the loop. That is, after the last file, it starts again with the first file and keeps looping. What is wrong?

Sub morningstar_open_and_save_only_VBA()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim filename As String
Dim path_to_save As String
Dim FldrPicker As FileDialog
Dim w As Long
Dim StartTime As Double
Dim SecondsElapsed As Double

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
  Application.ScreenUpdating = False
    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsx*"

'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)
    Set cmd = Application.CommandBars("Cell").Controls("Refresh All")
    cmd.Execute


    'Ensure Workbook has opened before moving on to next line of code

    wb.Close savechanges:=True

    'Ensure Workbook has closed before moving on to next line of code
    DoEvents

    'Get next file name
    myFile = Dir
  Loop
SecondsElapsed = Round(Timer - StartTime, 2)
'Message Box when tasks are completed
  MsgBox "Task Complete! in " & SecondsElapsed & " seconds"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 94

Answers (1)

Puygrenier Solann
Puygrenier Solann

Reputation: 190

Just made some research , I guess you can adpat the code from another post (see link : Get list of Excel files in a folder using VBA)

You can adapt the loop to your need and it's more elegant !

For Each oFile In oFiles

Next

I hope it help ! Take care !

Upvotes: 1

Related Questions