✨ Once Upon a Prompt…

It was a regular evening when I sat in front of my laptop, staring at dozens of folders full of Excel files. My desktop was clean, but my work wasn’t. Reports, templates, client sheets—all scattered across subfolders like hidden treasure.

I sighed and thought:
“If only there was a way to collect every Excel file from any folder and place them neatly into one Desktop folder… automatically!”

That’s when the idea struck me—why not ask AI for help?

 

???? The Prompt I Asked

I typed:

“I want to create a new Excel file, where it will pop up a window to ask a folder location. In that folder, it will check each and every file and if an Excel file is there then copy it into a new folder on desktop. Folder name will be ‘All excel files’. Give me VBA code.”

And then… magic happened.

 

???? The Answer That Arrived

Out of the digital mist, the VBA code appeared—like a genie responding to a wish.

Option Explicit

' ====== MAIN ENTRY ======
Sub CollectExcelFiles()
    Dim srcPath As String
    Dim destPath As String
    Dim fso As Object ' Scripting.FileSystemObject

    On Error GoTo CleanFail
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Preparing..."

    ' Ask user to pick a source folder
    srcPath = PickFolder("Select the SOURCE folder to scan for Excel files:")
    If Len(srcPath) = 0 Then
        MsgBox "No folder selected. Operation cancelled.", vbInformation
        GoTo CleanExit
    End If

    ' Create/ensure Desktop\All excel files
    destPath = EnsureDesktopTargetFolder("All excel files")
    If Len(destPath) = 0 Then
        MsgBox "Could not create or access the destination folder on Desktop.", vbCritical
        GoTo CleanExit
    End If

    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(srcPath) Then
        MsgBox "The selected folder no longer exists." & vbCrLf & srcPath, vbCritical
        GoTo CleanExit
    End If

    Application.StatusBar = "Scanning and copying Excel files..."
    CopyExcelFilesRecursive fso.GetFolder(srcPath), destPath

    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Done! All Excel files were copied to:" & vbCrLf & destPath, vbInformation
    Exit Sub

CleanFail:
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MsgBox "Error: " & Err.Description, vbCritical

CleanExit:
    Application.ScreenUpdating = True
    Application.StatusBar = False
End Sub

' ====== FOLDER PICKER ======
Private Function PickFolder(ByVal promptText As String) As String
    Dim fd As FileDialog
    On Error GoTo SafeExit
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .Title = promptText
        .AllowMultiSelect = False
        If .Show = -1 Then
            PickFolder = .SelectedItems(1)
        Else
            PickFolder = vbNullString
        End If
    End With
SafeExit:
End Function

' ====== ENSURE DESKTOP\TARGET FOLDER ======
Private Function EnsureDesktopTargetFolder(ByVal folderName As String) As String
    Dim fso As Object ' Scripting.FileSystemObject
    Dim desktopPath As String, destPath As String

    desktopPath = Environ$("USERPROFILE") & "\Desktop"
    If Len(Dir(desktopPath, vbDirectory)) = 0 Then
        ' Fallback to known folder if Desktop not resolved
        desktopPath = CurDir$
    End If

    destPath = desktopPath & "\" & folderName
    Set fso = CreateObject("Scripting.FileSystemObject")
    On Error Resume Next
    If Not fso.FolderExists(destPath) Then
        fso.CreateFolder destPath
    End If
    On Error GoTo 0

    If fso.FolderExists(destPath) Then
        EnsureDesktopTargetFolder = destPath
    Else
        EnsureDesktopTargetFolder = vbNullString
    End If
End Function

' ====== RECURSIVE COPY ======
Private Sub CopyExcelFilesRecursive(ByVal srcFolder As Object, ByVal destPath As String)
    Dim f As Object    ' Scripting.File
    Dim subFld As Object ' Scripting.Folder
    Dim total As Long

    ' Copy files in current folder
    For Each f In srcFolder.Files
        If IsExcelFile(f.Name) Then
            If Left$(f.Name, 2) <> "~$" Then ' skip temp/lock files
                CopyFileNoOverwrite f.Path, destPath
            End If
        End If
    Next f

    ' Recurse into subfolders
    For Each subFld In srcFolder.SubFolders
        ' Skip hidden/system folders if desired (optional):
        ' If (subFld.Attributes And 2) = 0 And (subFld.Attributes And 4) = 0 Then
            CopyExcelFilesRecursive subFld, destPath
        ' End If
    Next subFld
End Sub

' ====== EXCEL EXTENSION CHECK ======
Private Function IsExcelFile(ByVal fileName As String) As Boolean
    Dim ext As String
    ext = LCase$(GetExtension(fileName))
    ' Include common Excel-related extensions
    Select Case ext
        Case "xls", "xlsx", "xlsm", "xlsb", "xltx", "xltm", "xlt", "xlam"
            IsExcelFile = True
        Case Else
            IsExcelFile = False
    End Select
End Function

Private Function GetExtension(ByVal fileName As String) As String
    Dim pos As Long
    pos = InStrRev(fileName, ".")
    If pos > 0 Then
        GetExtension = Mid$(fileName, pos + 1)
    Else
        GetExtension = vbNullString
    End If
End Function

' ====== COPY WITHOUT OVERWRITING (adds " (1)", " (2)", ...) ======
Private Sub CopyFileNoOverwrite(ByVal srcFile As String, ByVal destFolder As String)
    Dim fso As Object ' Scripting.FileSystemObject
    Dim baseName As String, ext As String, destPath As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    baseName = fso.GetBaseName(srcFile)
    ext = fso.GetExtensionName(srcFile)

    destPath = destFolder & "\" & baseName & IIf(ext <> "", "." & ext, "")
    destPath = GetUniquePath(destPath)

    On Error Resume Next
    fso.CopyFile srcFile, destPath, False
    On Error GoTo 0

    Application.StatusBar = "Copied: " & srcFile
    DoEvents
End Sub

Private Function GetUniquePath(ByVal fullPath As String) As String
    Dim fso As Object ' Scripting.FileSystemObject
    Dim pathNoExt As String, ext As String, n As Long, pos As Long, candidate As String

    Set fso = CreateObject("Scripting.FileSystemObject")

    ' Split into name + extension
    pos = InStrRev(fullPath, ".")
    If pos > 0 Then
        pathNoExt = Left$(fullPath, pos - 1)
        ext = Mid$(fullPath, pos)
    Else
        pathNoExt = fullPath
        ext = ""
    End If

    candidate = fullPath
    n = 1
    While fso.FileExists(candidate)
        candidate = pathNoExt & " (" & n & ")" & ext
        n = n + 1
    Wend

    GetUniquePath = candidate
End Function

How It Works

1. Pick a Folder – A dialog pops up asking you to choose the source folder.

2. Destination Creation – A new folder named All excel files is created on your Desktop.

3. Recursive Search – The macro goes through all files and subfolders inside your chosen folder.

4. Excel File Detection – It checks extensions (xls, xlsx, xlsm, xlsb, etc.) and skips temporary files (~$).

5. Safe Copying – Files are copied to the destination. If duplicates exist, it auto-renames them as (1), (2), ....

Advantages of This Approach

• Time-saving – Collects hundreds of files in seconds.

• Error-free – Prevents missed or overwritten files.

• Customizable – You can tweak it to copy other file types (e.g., PDFs, Word).

Conclusion

With this VBA macro, you no longer need to manually search, drag, and drop Excel files. Just run the macro, and within moments, all Excel files will be neatly collected in one Desktop folder.

This kind of automation highlights the real power of Excel and VBA—making everyday tasks smarter and faster.