Loop Through all files in folder only finding first file

**Optional_karl** · 09-02-2016, 05:31 AM

Hello Everyone,

I've got the below code that is looping through a series of XLS files in a specified location and saving them as a CSV file in a different location. The code works fine for the first file, but the loop doesn't find the next file. i have no idea what i'm missing right now....

There are several files in the pickup location, all '.xls', but only the first one, alphabetically, is being pulled into the Dir()

Sub IdentifyFiles()


'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    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 = "*.xls"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Check Save Location
Dim SaveL As String

SaveL = "C:\Users\" & Environ("USERNAME") & "\Desktop\CSVOutput"
fCheckPath SaveL, True

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
   'Run Conversion Sub
   
   
   Dim SaveN As String
   SaveN = Left(wb.Name, Len(wb.Name) - 4)
   SaveFile (SaveL & "\" & SaveN)
      
   
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents
      
     Next i

    'Get next file name
      myFile = Dir()
      Loop
  MsgBox "Files Converted succesfully!", vbOKOnly, "Complete"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
  'All Files Converted
  
End Sub

Any help please?

**YasserKhalil** · 09-02-2016, 07:28 AM

I don't know what is the problem in your code.. I think you missed some parts for the code
Can you provide us the link for this code

Generally try the following code to do the task

Sub CSVToXLS()
    Dim fPath  As String, fPathDONE As String, fCOUNT As Long
    Dim fName  As String, fType     As String
    Dim fAfter As String, NwName    As String
    
    fPath = ThisWorkbook.Path & "\Test\"
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
    
    fPathDONE = fPath & "\Converted\"
    MakeFolders fPathDONE
    
    fName = Dir(fPath & "*.CSV")
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Do While Len(fName) > 0
        NwName = Left(fName, InStrRev(fName, ".") - 1)
        Workbooks.Open fPath & fName
        ActiveSheet.Name = NwName
        ActiveWorkbook.SaveAs fPath & NwName & ".xls", FileFormat:=xlNormal
        ActiveWorkbook.Close
        
        Name fPath & fName As fPathDONE & fName
        
        fCOUNT = fCOUNT + 1
        fName = Dir()
    Loop
    
    MsgBox "A Total Of " & fCOUNT & " Files Were Processed"
    Application.ScreenUpdating = True
End Sub

Function MakeFolders(MyStr As String)
    Dim MyArr   As Variant
    Dim pNum    As Long
    Dim pBuf    As String
    
    On Error Resume Next
    
    MyArr = Split(MyStr, "\")
    pBuf = MyArr(LBound(MyArr)) & "\"
    For pNum = LBound(MyArr) + 1 To UBound(MyArr)
        pBuf = pBuf & MyArr(pNum) & "\"
        MkDir pBuf
    Next pNum
End Function

Loop Through all files in folder only finding first file

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop Through all files in folder only finding first file

Re: Loop Through all files in folder only finding first file

Thread Information

Users Browsing this Thread

Similar Threads

Choose a Folder and Loop a Sub for all files in the chosen folder

[SOLVED] Choose a folder and loop through all files in the chosen folder

[SOLVED] Loop Through Folder, Create Emails with Sub Folder Names in Subject, Attach files in sub

Choose folder & loop through all files to copy - folder is subfolder in network drive

Loop through files in a folder and copy 2 rows from each file(xlsx) in a master file(xlsx)

Loop through each folder and then loop through each file to find file with certain keyword

VBA Loop for a folder of all the files, Loop all the worksheet in each workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions