VBA Question

Nouf N

New Member
Joined
Feb 11, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am new in using VBA in Excel. I have three related questions:
- How can I write in one cell in excel file the names for many excel files at one folder?
- How can I arrange and list the names in the cell?
- How can I repeat the task in a different cell at the same excel but for different folder files?

Thanks
 
Hello,

I am new in using VBA in Excel. I have three related questions:
- How can I write in one cell in excel file the names for many excel files at one folder?
- How can I arrange and list the names in the cell?
- How can I repeat the task in a different cell at the same excel but for different folder files?

Thanks
Welcome to Mr Excel.

Excel has row height maximum of 409 so a single cell may not be able to hold as many file names as you wish. It does depend on font type and size though.

Put this code into the main code module in a new Excel macro enabled workbook saved in a folder with lots of Excel files,

Run the code and cell B10 will have a list of the files.

If this is what you you want then let me know and it can be enhanced.

VBA Code:
Public Sub subListFiles()
Dim fso, objFolder, objFiles
Dim strPath
Dim objItem As Object
Dim strFiles As String

  ActiveWorkbook.Save
 
  ' Create a FileSystemObject.
  Set fso = CreateObject("Scripting.FileSystemObject")
   
  ' Define folder we want to list files from.
  strPath = ActiveWorkbook.Path
  
  Set objFolder = fso.GetFolder(strPath)
  Set objFiles = objFolder.files
   
  ' Loop through each file.
  For Each objItem In objFiles
  
    fso.GetExtensionName (strPath & objItem.Name)
   
    ' Create a list of files.
    
    If Left(fso.GetExtensionName(strPath & objItem.Name), 3) = "xls" Then
    
      strFiles = strFiles & objItem.Name & Chr(10)
    
    End If
        
  Next
  
  ' Write list of files to a cell.
  Range("B10").Value = strFiles
  
  Set objFiles = Nothing
  Set objFolder = Nothing
  Set fso = Nothing
  
End Sub
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top