CODEWITHSUNDEEP
excelvbafileforeachfso
Pete
Pete

Reputation: 13

Why will be the last file the first one in vba for each?

I would like to list the files and subfolders to an Excel sheet with vba macro. The listing is working, but the last file of last folder moves to first place of actual folder in the list.

Here is the result: enter image description here

And here is the code:

If selectedFolder.Files.Count = 0 Then
            For Each origSubFolder In selectedFolder.SubFolders
                'Create backup subfolder
                copiedSubFolder = copiedFilesDir & "\" & origSubFolder.Name & affix
                fso.CreateFolder copiedSubFolder
                
                'Recording folders to Excel file
                rfSht.Range("C" & r).Value = origSubFolder
                rfSht.Range("M" & r).Value = copiedSubFolder
                r = r + 1
                
                For Each File In origSubFolder.Files
                    'Save As original files as xlsx
                    fileName = fso.GetFileName(File)
                    fileNameWOExt = Left(fileName, (InStrRev(fileName, ".", -1, vbTextCompare) - 1))
                        'fileNameWOExt = Left(fileName, InStr(fileName, ".") - 1)
                    fileNameWAffix = fileNameWOExt & affix
                    Set owb = Workbooks.Open(File)
                    owb.SaveAs fileName:=copiedSubFolder & "\" & fileNameWAffix, FileFormat:=51
                    ActiveWorkbook.Close
                    
                    'Recording files to Excel file
                    rfSht.Range("D" & r).Value = File
                    rfSht.Range("N" & r).Value = copiedSubFolder & "\" & fileNameWAffix & ".xlsx"
                    r = r + 1
                Next
            Next
            MsgBox "Task completed", vbInformation
        Else

I was looking for the problem in the for each loop or "r=r+1", but always get this. Could you help me?

Upvotes: 0

Views: 73

Answers (1)

Oliviero Domenighini
Oliviero Domenighini

Reputation: 26

You could do 2 things 1 is sorting the output in an array on VBA and then print the Array. else you could also sort output on excel. I would recommend the first one.

Upvotes: 0

Related Questions