VBA Question

[Nouf N]

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

 

[HighAndWilder]

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.

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